Hi,

I am having an issue with the ordering of my xml tags. Please see my example
below:

SELECT 1 as Tag, 0 as Parent,
NULL as [ifti-draList!1!Item1!ELEMENT],
NULL as [ifti-draList!1!Item2!ELEMENT],
NULL as [Item1!2!Item1A!ELEMENT],
NULL as [Item1!2!Item1B!ELEMENT],
NULL as [Item2!3!Item2A!ELEMENT],
NULL as [Item2A!4!Item2AA!ELEMENT],
NULL as [Item2!3!Item2B!ELEMENT]
from table1
UNION ALL
SELECT 2 as Tag, 1 as Parent,
NULL,
NULL,
'Item 1A',
'Item 1B',
NULL,
NULL,
NULL
from table1
UNION ALL
SELECT 3 as Tag, 1 as Parent,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'Item 2B'
from table1
UNION ALL
SELECT 4 as Tag, 3 as Parent,
NULL,
NULL,
NULL,
NULL,
NULL,
'Item 2AA',
NULL
from table1
FOR XML EXPLICIT

The resulting xml from this query is below:

<ifti-draList>
<Item1>
<Item1A>Item 1A</Item1A>
<Item1B>Item 1B</Item1B>
</Item1>
<Item2>
<Item2B>Item 2B</Item2B> -- This should be below tag Item2A??
<Item2A>
<Item2AA>Item 2AA</Item2AA>
</Item2A>
</Item2>
</ifti-draList>

The Item2B tag appears above the Item2A tag, this should be the other way
round.

Can someone please help me get the ordering correct?

Regards,

Ben

What version of SQL Server are you using?

"Benzine" wrote:

Hi Bob,

Thanks for your reply.
I'm using ms sql 2005. Cheers.



"Bob" wrote:

Hi Bob,

Thanks for your reply.
I am using ms sql 2005. Cheers.

"Bob" wrote:

Being as you're using SQL 2005, you may want to look at FOR XML PATH, where
it's so much easier to do this type of thing, eg

SELECT
'Item 1A' AS "Item1/Item1A",
'Item 1B' AS "Item1/Item1B",
'Item 2AA' AS "Item2/Item2AA",
'Item 2B' AS "Item2/Item2B"

FOR XML PATH('ifti-draList')

"Benzine" wrote:

Thanks for your suggestion.
I will try this and let you know howI get on.

Ben

"Bob" wrote:

Hi Bob,

Another quick question:
When using FOR XML PATH how would I specify header elements in the xml file,
please see my example:

<ifti-draList>
<fileName>File1</fileName> --Header record
<recordCount>2</recordCount> --Header record
<ifti-dra id="1">
<customerId>1</customerId>
<customerName>John Doe</customerName>
</ifti-dra>
<ifti-dra id="2">
<customerId>2</customerId>
<customerName>Jane Doe</customerName>
</ifti-dra>
</ifti-draList>

Regards,

Ben


"Bob" wrote:

SELECT
'File1' AS "fileName",
'2' AS recordCount,
'Item 1A' AS "Item1/Item1A",
'Item 1B' AS "Item1/Item1B",
'Item 2AA' AS "Item2/Item2AA",
'Item 2B' AS "Item2/Item2B"

FOR XML PATH('ifti-draList')

Have a look at Books Online (BOL) for help on FOR XML PATH.

"Benzine" wrote: