使用FOR XML PATH从XMLTYPE列中进行选择时如何删除列名

I have a table with a XMLTYPE column named 'InvoiceXML'.

我有一个名为'InvoiceXML'的XMLTYPE列的表。

The data in this column is XML in the form:

此列中的数据是XML格式：

<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?

如何停止出现在输出中的列名InvoiceXML？

3 个解决方案

#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.

编辑1子查询（选择T.invoiceXML）没有列名，因此将其删除。

#2

Try:

尝试：

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

#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')`~~

#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')