How to remove column name when selecting from a XMLTYPE column using FOR XML PATH

Question

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(''), 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?

Florid

Answer 1

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.

Selectee

Nice. You should probably add why your solution works: the subquery is given no name, so it doesn't have a column name to put in the containing element, causing it to be removed. – Rush

Answer 2

Try:

SELECT cast(cast(InvoiceXML as nvarchar(max)) + '' as XML)
FROM whatever
FOR XML PATH(''), ROOT('Invoices')

Dissimilar

Don't think you're allowed to concatenate a string directly onto an XML column. You'd need to double cast: cast(cast(InvoiceXML as nvarchar(max)) + '' as XML) – Ott

+1 After further review, I think it can be simplified even further. I looks like cast(InvoiceXML as XML) is sufficient. – Ott

I'd downvote if I had enough rep because the cast to nvarchar(max) fundamentally breaks the XML type due to length restrictions. varchar(max), nvarchar(max) and xml can store 2GB but nvarchar uses 2x the storge per character. References: technet.microsoft.com/en-us/library/ms186939.aspx and msdn.microsoft.com/en-us/library/ms187339.aspx – Blether

Answer 3

Try this:

SELECT InvoiceXML.query('//Invoice')
  FROM <YOUR_TABLE>
FOR XML PATH('')

Try specifying a xpath query to invoice in the FPR XML PATH e.g: `FOR XML PATH('//InvoiceXML')`

London

Does that work? When I tried, it gives me: Row name '//InvoiceXML' contains an invalid XML identifier as required by FOR XML; '/'(0x002F) is the first character at fault. Removing the double-slash results in <InvoiceXML> wrapping each row twice. – Rush

Didn't test the earlier version of the answer, on verification it failed :). Updated the post with working version. Thx – London