I have generated XML from a SQL Server FOR XML PATH
statement as shown here:
USE MySQLDB
SELECT *
FROM BillTable
FOR XML PATH ('BillAdd'), ROOT ('BillAddRq')
And this is the result:
<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 & 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>
Now, I'd like to encapsulate the above with these nodes:
<?xml version="1.0" encoding="utf-8"?>
<?qbxml version="15.0"?>
<QBXML>
<QBXMLMsgsRq one rror="stopOnError">
//above generated xml//
</QBXMLMsgsRq>
</QBXML>
How will I achieve this in a SQL Query I created above?
I am new to SQL Server and XML. I am trying to generate this XML directly from my database and vice versa to make it more efficient and faster — let my SQL directly communicate with XML.
ATTEMPT 1:
USE MySQLDB;
GO
DECLARE @myDoc XML;
SET @myDoc = '<QBXML>
<QBXMLMsgsRq one rror="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;
ATTEMPT 2:
USE MySQLDB;
GO
DECLARE @myDoc XML;
SET @myDoc = '<QBXML>
<QBXMLMsgsRq one rror="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;
CodePudding user response:
There are many ways to construct your XML result, consider the following three alternatives...
Use XML.modify() to insert the BillTable XML into an XML scalar variable (which includes the ?qbxml
XML processing instruction):
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 one rror="stopOnError">
</QBXMLMsgsRq>
</QBXML>';
set @myDoc.modify('
insert sql:variable("@BillTableXml")
into (/QBXML/QBXMLMsgsRq)[1]
');
select @myDoc as Result;
Use a nested query to construct the entire XML result (which does not, however, include the ?qbxml
XML processing instruction):
select
'stopOnError' as [QBXML/QBXMLMsgsRq/@onError],
(
select *
from BillTable
for xml path('BillAdd'), root('BillAddRq'), type
) as [QBXML/QBXMLMsgsRq]
for xml path('');
Or use an XQuery to construct the entire XML result (which also includes the ?qbxml
XML processing instruction):
select BillTableXml.query('
<?qbxml version="15.0"?>,
<QBXML>
<QBXMLMsgsRq one rror="stopOnError">
{ /BillAddRq }
</QBXMLMsgsRq>
</QBXML>
') as Result
from (
select *
from BillTable
for xml path('BillAdd'), root('BillAddRq'), type
) Data (BillTableXml);