For XML Explicit question in sql server 2000

Question

question

Husain asked Dec 2, '09

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-sql sql-server-2000 xml

10 |1200

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

Answer 1 · 2009-12-02T14:38:39Z

Kev Riley answered Dec 2, '09

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

10 |1200

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

Tom Staab ♦ commented · Dec 02, 2009 at 03:26 PM

Are you sure you covered all of the options? ;) Great answer, Kev.

0 ·

Kev Riley ♦♦ commented · Dec 02, 2009 at 03:56 PM

probably not, including the one the OP wants :)

0 ·

question