x

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

more ▼

asked Dec 02, 2009 at 01:12 PM in Default

Husain gravatar image

Husain
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Dec 02, 2009 at 02:38 PM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

Are you sure you covered all of the options? ;) Great answer, Kev.
Dec 02, 2009 at 03:26 PM Tom Staab
probably not, including the one the OP wants :)
Dec 02, 2009 at 03:56 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x474
x150

asked: Dec 02, 2009 at 01:12 PM

Seen: 2055 times

Last Updated: Dec 02, 2009 at 01:52 PM