Using FOR XML PATH with strange data structure

I see where you're trying to go with this Peter and it breaks a few rules of how SQL will automatically build an XML file for you.

My best recommendation without seeing some ddl/sample data will be a little generic on the advice, but hopefully will get you in the right direction.

You're looking at doing a series of appends to a final xml output. Build out the header as needed. You're also mixing tagtypes

<Text ColumnNo="1">20078</Text>

which will make it more difficult. With sample data for the row information someone can help put that together for you, but look into FOR XML TYPE and FOR XML EXPLICIT. I'm relatively sure you're going to need to work with those to get this to behave.

Hi again,

Im really grateful for you guys taking interest in this, so I have elaborated a little to give a better examples of what I am trying to do.

The basic problem I have is to generate this little segment with resorting to EXPLICIT or even start witing some kind of C# code. This is part of a routine where we create XML invoices from T-SQL Scripts. Here is the segment I am struggling with, with some non-important parts removed (the format is non-negotiable):

<InvoicePrintBlock>

<VWPrintBlock>

<ColumnHeadLine>

<Text ColumnNo="1">Item No</Text>

<Text ColumnNo="2">Decription</Text>

<Text ColumnNo="3">Amount Due</Text>

</ColumnHeadLine>

<Row>

<Text ColumnNo="1">20078</Text>

<Text ColumnNo="2">PRODUKT 2007800001</Text>

<Text ColumnNo="3">0.58</Text>

</Row>

<Row>

<Text ColumnNo="1">20079</Text>

<Text ColumnNo="2">PRODUKT 2007900001</Text>

<Text ColumnNo="3">10.00</Text>

</Row>

<Row>

<Text ColumnNo="2">Rounding</Text>

<Text ColumnNo="3">0.42</Text>

</Row>

</VWPrintBlock>

To make clear what i have done this is a script to create a test table and insert some data:

/* Create test tables and insert some data */

create table InvoiceHeader (No int, CustomerNo Int)

insert into InvoiceHeader (No, CustomerNo) Values (123,1000)

create table InvoiceLine (No int, LineNumber int, Item nvarchar(10), Description nvarchar(50), Amount decimal(20,4))

insert into InvoiceLine (No, LineNumber, Item, Description, Amount)

Values(1,100,'Bike','BiCycle',1100.00)

insert into InvoiceLine (No, LineNumber, Item, Description, Amount)

Values(1,200,'Helmet','BiCycle Helmet Black',59.99)

/* Create a table to hold the column names */

create table ColumnNames (id int, ColName nvarchar(20))

insert into ColumnNames (id, ColName) Values (1,'Item No')

insert into ColumnNames (id, ColName) Values (2,'Description')

insert into ColumnNames (id, ColName) Values (1,'Amount Due')

Using this I can create a sample without any item rows yet:

There is also a surrounding Invoice header part, but start looking at the ColumnHeadline part and you get what I mean.

Also, never mind the fact that I have not connected the Invoice Header to the Invoice Lines, in the real script I have a where clause to get the correct detail lines for each invoice, but for simplicity and clarity that has been left out here.

Select

No as 'Invoice/InvoiceNumber',

CustomerNo as 'Invoice/CustomerNumber',

(SELECT

id as '@ColumnNo',

ColName as '*'

FROM ColumnNames

FOR XML PATH('Text'), TYPE) as 'Invoice/ColumnHeadLine'

FROM InvoiceHeader

FOR XML PATH('InvoiceMessage')

Produces this which is correct so far:

<InvoiceMessage>

<Invoice>

<InvoiceNumber>123</InvoiceNumber>

<CustomerNumber>1000</CustomerNumber>

<ColumnHeadLine>

<Text ColumnNo="1">Item No</Text>

<Text ColumnNo="2">Description</Text>

<Text ColumnNo="1">Amount Due</Text>

</ColumnHeadLine>

</Invoice>

</InvoiceMessage>

Now, the trick is to get the Item rows in place. My attempt was to do this:

Select

No as 'Invoice/InvoiceNumber',

CustomerNo as 'Invoice/CustomerNumber',

(SELECT

id as '@ColumnNo',

ColName as '*'

FROM ColumnNames

FOR XML PATH('Text'), TYPE) as 'Invoice/ColumnHeadLine',

(SELECT 1 as '@ColumnNo',

Item as '*'

FROM InvoiceLine

FOR XML PATH('Row'), TYPE) as 'Invoice/ItemRows'

FROM InvoiceHeader

FOR XML PATH('InvoiceMessage')

Which looks promising:

<InvoiceMessage>

<Invoice>

<InvoiceNumber>123</InvoiceNumber>

<CustomerNumber>1000</CustomerNumber>

<ColumnHeadLine>

<Text ColumnNo="1">Item No</Text>

<Text ColumnNo="2">Description</Text>

<Text ColumnNo="1">Amount Due</Text>

</ColumnHeadLine>

<ItemRows>

<Row ColumnNo="1">Bike</Row>

<Row ColumnNo="1">Helmet</Row>

</ItemRows>

</Invoice>

</InvoiceMessage>

However, when I proceed to the other columns 2 and 3, I get stuck:

Select

No as 'Invoice/InvoiceNumber',

CustomerNo as 'Invoice/CustomerNumber',

(SELECT

id as '@ColumnNo',

ColName as '*'

FROM ColumnNames

FOR XML PATH('Text'), TYPE) as 'Invoice/ColumnHeadLine',

(SELECT 1 as '@ColumnNo',

Item as '*',

2 as '@ColumnNo',

Description as '*'

FROM InvoiceLine

FOR XML PATH('Row'), TYPE) as 'Invoice/ItemRows'

FROM InvoiceHeader

FOR XML PATH('InvoiceMessage')

Gives me this:

Msg 6852, Level 16, State 1, Line 1

Attribute-centric column '@ColumnNo' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

So I wandered away and figured I could hard-code the desired XML element like this:

Select

No as 'Invoice/InvoiceNumber',

CustomerNo as 'Invoice/CustomerNumber',

(SELECT

id as '@ColumnNo',

ColName as '*'

FROM ColumnNames

FOR XML PATH('Text'), TYPE) as 'Invoice/ColumnHeadLine',

(SELECT

'<Text ColumnNo="1">' as 'data()',

Item as '*',

'<Text ColumnNo="2">' as 'data()',

Description as '*'

FROM InvoiceLine

FOR XML PATH('Row'), TYPE) as 'Invoice/ItemRows'

FROM InvoiceHeader

FOR XML PATH('InvoiceMessage')

But that gives me this, which is where my first question started (I have replace & with $ to show where the errors are)

<InvoiceMessage>

<Invoice>

<InvoiceNumber>123</InvoiceNumber>

<CustomerNumber>1000</CustomerNumber>

<ColumnHeadLine>

<Text ColumnNo="1">Item No</Text>

<Text ColumnNo="2">Description</Text>

<Text ColumnNo="1">Amount Due</Text>

</ColumnHeadLine>

<ItemRows>

<Row>$lt;Text ColumnNo="1"$gt;Bike$lt;Text ColumnNo="2"$gt;BiCycle</Row>

<Row>$lt;Text ColumnNo="1"$gt;Helmet$lt;Text ColumnNo="2"$gt;BiCycle Helmet Black</Row>

</ItemRows>

</Invoice>

</InvoiceMessage>

Given you hint about casting, this is what I have come up with so far, and it seem to do the right thing.

Select

No as 'Invoice/InvoiceNumber',

CustomerNo as 'Invoice/CustomerNumber',

(SELECT

id as '@ColumnNo',

ColName as '*'

FROM ColumnNames

FOR XML PATH('Text'), TYPE) as 'Invoice/ColumnHeadLine',

(SELECT

cast('<Text ColumnNo="1">' + Item + '</Text>' as XML) as '*',

cast('<Text ColumnNo="2">' + Description + '</Text>' as XML) as '*',

cast('<Text ColumnNo="3">' + cast(Amount as nvarchar(10)) + '</Text>' as XML) as '*'

FROM InvoiceLine

FOR XML PATH('Row'), TYPE) as 'Invoice/ItemRows'

FROM InvoiceHeader

FOR XML PATH('InvoiceMessage')

Produces this:

<InvoiceMessage>

<Invoice>

<InvoiceNumber>123</InvoiceNumber>

<CustomerNumber>1000</CustomerNumber>

<ColumnHeadLine>

<Text ColumnNo="1">Item No</Text>

<Text ColumnNo="2">Description</Text>

<Text ColumnNo="1">Amount Due</Text>

</ColumnHeadLine>

<ItemRows>

<Row>

<Text ColumnNo="1">Bike</Text>

<Text ColumnNo="2">BiCycle</Text>

<Text ColumnNo="3">1100.0000</Text>

</Row>

<Row>

<Text ColumnNo="1">Helmet</Text>

<Text ColumnNo="2">BiCycle Helmet Black</Text>

<Text ColumnNo="3">59.9900</Text>

</Row>

</ItemRows>

</Invoice>

</InvoiceMessage>

So I'm happy,and thankful for the advice that lead me in the right direction!

Given what you have seen - do you think there is a more elegant solution?

Peter,

I'm not sure about elegancy but building XML by concatenating strings and tags requires encoding of string values. I'd recommend using XQuery here:

Select

No as 'Invoice/InvoiceNumber',

CustomerNo as 'Invoice/CustomerNumber',

(SELECT

id as '@ColumnNo',

ColName as '*'

FROM ColumnNames

FOR XML PATH('Text'), TYPE) as 'Invoice/ColumnHeadLine',

(SELECT

cast('' as xml).query('

<Text ColumnNo="1">{sql:column("Item")}</Text>,

<Text ColumnNo="2">{sql:column("Description")}</Text>,

<Text ColumnNo="3">{sql:column("Amount")}</Text>

')

FROM InvoiceLine

FOR XML PATH('Row'), TYPE) as 'Invoice/ItemRows'

FROM InvoiceHeader

FOR XML PATH('InvoiceMessage')

Nice solution Anton.