How can I combine CTE with a FOR XML clause?

Question

I am trying to generate some XML with different levels of nesting, and despite being overly simplified, the XML output will be format-free:

<invoice number="1"> <charge code="foo" rate="123.00"> <surcharge amount="10%" /> </charge> <charge code="bar" /> </invoice> 

The database schema I inherited for this has a charge stored in different tables, which means that the surcharges are stored differently based on the table from which the charge was made.

Given that you cannot use UNION with FOR XML , I made several UNION ing in CTE, so something like:

 WITH Charges ( [@code], [@rate], surcharge, InvoiceId ) AS ( SELECT code AS [@Code], amount AS [@rate], NULL as surcharge, InvoiceId FROM item.charges UNION ALL SELECT code AS [@Code], amount AS [@rate], ( SELECT amount AS [@amount] FROM order.surcharges os WHERE oc.ChargeId = os.ChargeId FOR XML PATH('surcharge'), TYPE ), InvoiceId FROM order.charges oc ) SELECT Number AS [@number], ( SELECT [@code], [@rate], surcharge FROM Charges WHERE Charges.InvoiceId = i.InvoiceId ) FROM Invoices i FOR XML PATH( 'invoice' ), TYPE 

Now this is incredibly close, giving (note the nested <surcharge> ):

 <invoice number="1"> <charge code="foo" rate="123.00"> <surcharge> <surcharge amount="10%" /> </surcharge> </charge> <charge code="bar" /> </invoice> 

But I need to find a way to get the final query to include the value of the XML column, which will be considered as the content of the element, and not as a new element. Is this possible, or do I need to take a new approach?

Answer 1

It seems that naming the (fake) column as "*" will use the contents of this column as the contents of the element, so changing the SQL as shown below makes it work:

 WITH Charges ( [@code], [@rate], surcharge, InvoiceId ) AS ( SELECT code AS [@Code], amount AS [@rate], NULL as surcharge, InvoiceId FROM item.charges UNION ALL SELECT code AS [@Code], amount AS [@rate], ( SELECT amount AS [@amount] FROM order.surcharges os WHERE oc.ChargeId = os.ChargeId FOR XML PATH('surcharge'), TYPE ), InvoiceId FROM order.charges oc ) SELECT Number AS [@number], ( SELECT [@code], [@rate], surcharge AS [*] -- Thsi will embed the contents of the previously generated XML in here. FROM Charges WHERE Charges.InvoiceId = i.InvoiceId ) FROM Invoices i FOR XML PATH( 'invoice' ), TYPE 

Answer 2

You have a column query that returns mulitple rows (@charge, @rate and XML type). I would expect you to send a message to report an error:

You can only specify one expression in the selection list when a subquery is not entered with EXISTS.

However, this is easily fixed by moving the request to outer apply . To remove the double surcharge element, you can move the XML column names as close to the bottom as possible, for example:

 ;WITH Charges (code, rate, surcharge, InvoiceId) AS ( SELECT code, amount, NULL, InvoiceId FROM @charges UNION ALL SELECT code , amount , ( SELECT amount AS [@amount] FROM @surcharges os WHERE oc.ChargeId = os.ChargeId FOR XML PATH('surcharge'), TYPE ) , InvoiceId FROM @charges oc ) SELECT Number AS [@number] , c.code as [charge/@code] , c.rate as [charge/@rate] , c.surcharge as [charge] FROM @Invoices i outer apply ( SELECT code , rate , surcharge FROM Charges WHERE Charges.InvoiceId = i.InvoiceId ) c WHERE i.InvoiceID = 1 FOR XML PATH( 'invoice' ), TYPE 

This will print for example:

 <invoice number="1"> <charge code="1" rate="1" /> </invoice> <invoice number="1"> <charge code="1" rate="1"> <surcharge amount="1" /> </charge> </invoice> 

The first element comes from the top of the union, where surcharge = null .

Answer 3

I think you can do this by omitting the root type node in your "for the xml path (" surcharge ") instruction." That is, use the path for xml ('') instead.