How to remove column name when selecting from a XMLTYPE column using FOR XML PATH
I have a table with a XMLTYPE column named 'InvoiceXML'.
The data in this column is XML in the form:
<Invoice CustomerNum="1234" >
<CustomDeliveryDetails />
</Invoice>
When I do a
SELECT ... FOR XML PATH开发者_C百科(''), ROOT('Invoices')
I end up with:
<Invoices>
<InvoiceXML>
<Invoice CustomerNum="1234" >
<CustomDeliveryDetails />
</Invoice>
</InvoiceXML>
</Invoices>
How do I stop the column name InvoiceXML appearing in the output?
declare @T table (invoiceXML xml)
insert into @T values (
'<Invoice CustomerNum="1234" >
<CustomDeliveryDetails />
</Invoice>
')
insert into @T values (
'<Invoice CustomerNum="4321" >
<CustomDeliveryDetails />
</Invoice>
')
select (select T.invoiceXML)
from @T as T
for xml path(''), root('Invoices')
Edit 1 The subquery (select T.invoiceXML
) has no column name so it is removed.
Try:
SELECT cast(cast(InvoiceXML as nvarchar(max)) + '' as XML)
FROM whatever
FOR XML PATH(''), ROOT('Invoices')
Try this:
SELECT InvoiceXML.query('//Invoice')
FROM <YOUR_TABLE>
FOR XML PATH('')
精彩评论