如何在SQL生成的XML之上添加额外的XML节点

Question

我已经从SQL ServerFOR XML PATH语句生成了XML，如下所示:

USE MySQLDB
SELECT * 
FROM BillTable
FOR XML PATH ('BillAdd'), ROOT ('BillAddRq')

结果如下:

<BillAddRq>
<BillAdd>
<TxnID>2432-1071510295</TxnID>
<TimeCreated>2003-12-16T01:44:55</TimeCreated>
<TimeModified>2015-12-15T22:38:33</TimeModified>
<EditSequence>1450190313</EditSequence>
<TxnNumber>413</TxnNumber>
<VendorRef_ListID>E0000-933272656</VendorRef_ListID>
<VendorRef_FullName>Timberloft Lumber</VendorRef_FullName>
<APAccountRef_ListID>C0000-933270541</APAccountRef_ListID>
<APAccountRef_FullName>Accounts Payable</APAccountRef_FullName>
<TxnDate>2016-12-01T00:00:00</TxnDate>
<DueDate>2017-12-31T00:00:00</DueDate>
<AmountDue>80.50000</AmountDue>
<TermsRef_ListID>50000-933272659</TermsRef_ListID>
<TermsRef_FullName>1% 10 Net 30</TermsRef_FullName>
<IsPaid>0</IsPaid>
</BillAdd>
<BillAdd>
<TxnID>243A-1071510389</TxnID>
<TimeCreated>2003-12-16T01:46:29</TimeCreated>
<TimeModified>2015-12-15T22:38:33</TimeModified>
<EditSequence>1450190313</EditSequence>
<TxnNumber>414</TxnNumber>
<VendorRef_ListID>C0000-933272656</VendorRef_ListID>
<VendorRef_FullName>Perry Windows &amp; Doors</VendorRef_FullName>
<APAccountRef_ListID>C0000-933270541</APAccountRef_ListID>
<APAccountRef_FullName>Accounts Payable</APAccountRef_FullName>
<TxnDate>2016-12-02T00:00:00</TxnDate>
<DueDate>2018-01-01T00:00:00</DueDate>
<AmountDue>50.00000</AmountDue>
<TermsRef_ListID>10000-933272658</TermsRef_ListID>
<TermsRef_FullName>Net 30</TermsRef_FullName>
<IsPaid>0</IsPaid>
</BillAdd>
</BillAddRq>

现在，我想用这些节点封装上面的内容:

<?xml version="1.0" encoding="utf-8"?>
<?qbxml version="15.0"?>
<QBXML>
<QBXMLMsgsRq onError="stopOnError">
//above generated xml//
</QBXMLMsgsRq>        
</QBXML>

我如何在上面创建的SQL查询中实现这一点?

我是SQL Server和XML的新手。我正试图从我的数据库直接生成这个XML，反之亦然，使它更高效，更快-让我的SQL直接与XML通信。

尝试1:

USE MySQLDB;  
GO  
DECLARE @myDoc XML;         
SET @myDoc = '<QBXML>         
<QBXMLMsgsRq onError="stopOnError">                  
</QBXMLMsgsRq>         
</QBXML>'; 
SET @myDoc.modify('         
insert
-- instead of inserting string here.. I would like to insert here the query I made above
into (/QBXML/QBXMLMsgsRq)[1]');
SELECT @myDoc;

尝试2:

USE MySQLDB;  
GO  
DECLARE @myDoc XML;         
SET @myDoc = '<QBXML>         
<QBXMLMsgsRq onError="stopOnError">                  
</QBXMLMsgsRq>         
</QBXML>'; 
DECLARE @qry XML;
SET @qry = (SELECT * FROM BillTable FOR XML PATH ('BillAdd'), ROOT ('BillAddRq'));
-- SELECT @qry;
SET @myDoc.modify('insert @qry
into (/QBXML/QBXMLMsgsRq)[1]');
SELECT @myDoc;

Answer 1

构造XML结果的方法有很多，请考虑以下三种替代方法…

使用XML.modify()将BillTable XML插入到XML标量变量中(其中包括?qbxmlXML处理指令):

declare @BillTableXml xml = (
select *
from BillTable
for xml path('BillAdd'), root('BillAddRq')
);
declare @myDoc xml = '<?xml version="1.0" encoding="utf-8"?>
<?qbxml version="15.0"?>
<QBXML>
<QBXMLMsgsRq onError="stopOnError">
</QBXMLMsgsRq>        
</QBXML>';
set @myDoc.modify('
insert sql:variable("@BillTableXml")
into (/QBXML/QBXMLMsgsRq)[1]
');
select @myDoc as Result;

使用嵌套查询构造整个XML结果(但是，不包括?qbxmlXML处理指令):

select
'stopOnError' as [QBXML/QBXMLMsgsRq/@onError],
(
select *
from BillTable
for xml path('BillAdd'), root('BillAddRq'), type
) as [QBXML/QBXMLMsgsRq]
for xml path('');

或者使用XQuery构造整个XML结果(其中还包括?qbxmlXML处理指令):

select BillTableXml.query('
<?qbxml version="15.0"?>,
<QBXML>
<QBXMLMsgsRq onError="stopOnError">
{ /BillAddRq }
</QBXMLMsgsRq>        
</QBXML>
') as Result
from (
select *
from BillTable
for xml path('BillAdd'), root('BillAddRq'), type
) Data (BillTableXml);