question

Husain avatar image
Husain asked

For XML Explicit question in sql server 2000

I wrote my first t-sql for xml explicit in query analyzer using sql server 2000, but I get the error "Parent tag ID 3 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set."

However, when I remove the last union, it works fine!

I have looked into other forums, and found that it has something to do with sorting. but I have no sorting defined. Also, one forum mentioned values being nulls which is not the case either.

I have only reached the stage where I am trying to define my tags and haven't yet retreived any data from any tables.

Hope any one can help.

sql is as follows:

select 
1 as tag,
'' as parent,
'' as [TRNS!1],
'' as [TRN_IN!2],
'' as [TRN_ATRIB!3],
'' as [TRN_ORIGN!4],
'' as [TRN_ID!5]

union

select 
2 as tag,
1 as parent,
'' as [TRNS],
'' as [TRN_IN],
'' as [TRN_ATRIB],
'' as [TRN_ORIGN],
'' as [TRN_ID]

union

select 
3 as tag,
2 as parent,
'' as [TRNS],
'' as [TRN_IN],
'' as [TRN_ATRIB],
'' as [TRN_ORIGN],
'' as [TRN_ID]

union

select 
4 as tag,
2 as parent,
'' as [TRNS],
'' as [TRN_IN],
'' as [TRN_ATRIB],
'' as [TRN_ORIGN],
'' as [TRN_ID]

union

select 
5 as tag,
3 as parent,
'' as [TRNS],
'' as [TRN_IN],
'' as [TRN_ATRIB],
'' as [TRN_ORIGN],
'' as [TRN_ID]

for xml explicit

husainfb

t-sqlsql-server-2000xml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

Can you mock up how you see the XML structured.

The issue is in the definition, but to enable us to give you the correct answer we need the resulting XML.

for example, if you want this

<TRNS>
  <TRN_IN>
    <TRN_ATRIB></TRN_ATRIB>
    <TRN_ORIGN></TRN_ORIGN>
    <TRN_ID></TRN_ID>
  </TRN_IN>
</TRNS>

then change the last union to

select 
5 as tag,
2 as parent,
'' as [TRNS],
'' as [TRN_IN],
'' as [TRN_ATRIB],
'' as [TRN_ORIGN],
'' as [TRN_ID]


if you want this

<TRNS>
  <TRN_IN>
    <TRN_ATRIB></TRN_ATRIB>
    <TRN_ORIGN>
      <TRN_ID></TRN_ID>
    </TRN_ORIGN>
  </TRN_IN>
</TRNS>

then change the last union to

select 
5 as tag,
4 as parent,
'' as [TRNS],
'' as [TRN_IN],
'' as [TRN_ATRIB],
'' as [TRN_ORIGN],
'' as [TRN_ID]


if you want this

<TRNS>
  <TRN_IN>
    <TRN_ATRIB>
      <TRN_ID></TRN_ID>
    </TRN_ATRIB>
    <TRN_ORIGIN></TRN_ORIGIN>
  </TRN_IN>
</TRNS>

then change the whole SQL to

select 
1 as tag,
'' as parent,
'' as [TRNS!1],
'' as [TRN_IN!2],
'' as [TRN_ATRIB!3],
'' as [TRN_ID!4],
'' as [TRN_ORIGIN!5]

union

select 
2 as tag,
1 as parent,
'' as [TRNS],
'' as [TRN_IN],
'' as [TRN_ATRIB],
'' as [TRN_ID],
'' as [TRN_ORIGN]

union

select 
3 as tag,
2 as parent,
'' as [TRNS],
'' as [TRN_IN],
'' as [TRN_ATRIB],
'' as [TRN_ID],
'' as [TRN_ORIGN]

union

select 
4 as tag,
3 as parent,
'' as [TRNS],
'' as [TRN_IN],
'' as [TRN_ATRIB],
'' as [TRN_ID],
'' as [TRN_ORIGN]

union

select 
5 as tag,
2 as parent,
'' as [TRNS],
'' as [TRN_IN],
'' as [TRN_ATRIB],
'' as [TRN_ID],
'' as [TRN_ORIGN]

for xml explicit
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image Tom Staab ♦ commented ·
Are you sure you covered all of the options? ;) Great answer, Kev.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
probably not, including the one the OP wants :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.