How to add additional XML node on top of my SQL generated XML

Question

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

Now, I'd like to encapsulate the above with these nodes:

<?xml version="1.0" encoding="utf-8"?>
<?qbxml version="15.0"?>
<QBXML>
    <QBXMLMsgsRq onError="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 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;

ATTEMPT 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;

Please have a look here: https://stackoverflow.com/questions/4184163/sql-server-2008-add-xml-declaration-to-xml-output and use Google if necessary. There are already very many articles about this topic that should help. — Jonas Metzler, Apr 20 '22 at 04:19
@DhruvJoshi I am not so familiar but I will try to add string before and after.. — RickyBelmont, Apr 20 '22 at 04:29
You could also use [XML.modify()](https://learn.microsoft.com/en-us/sql/t-sql/xml/modify-method-xml-data-type) to [insert](https://learn.microsoft.com/en-us/sql/t-sql/xml/insert-xml-dml) the XML at the appropriate document location. — AlwaysLearning, Apr 20 '22 at 04:45
@AlwaysLearning I followed the documentation you shared but I am struggling to insert the query instead of string in the documentation. I will appreciate further help here. Please check my edited Attempt 1. Thanks. — RickyBelmont, Apr 20 '22 at 06:25
I created another attempt above but still not working. The @@qry is actually working but the how-to insert in @@myDoc.Modify is what I am missing something. — RickyBelmont, Apr 20 '22 at 06:59

score 1 · Accepted Answer · answered Apr 20 '22 at 10:30

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 onError="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 onError="stopOnError">
      { /BillAddRq }
    </QBXMLMsgsRq>        
  </QBXML>
') as Result
from (
  select *
  from BillTable
  for xml path('BillAdd'), root('BillAddRq'), type
) Data (BillTableXml);

@AlwaysLearing Thanks for these! I can say we're almost there. Most of them works except the `` which was not captured by the first solution. — RickyBelmont, Apr 20 '22 at 10:59
At last! I realized why `` is not necessary to be included in the xml above because it is a declaration of the xml itself. I tested the above result and it is working on my end. Thanks so much for this solutions. It works. — RickyBelmont, Apr 21 '22 at 00:08
Yes, sorry, I didn't mean to leave that in there. Internally the XML data type in SQL Server doesn't preserve the `` declaration because it's in a stored semi-parsed format - and so must have come from XML by definition. — AlwaysLearning, Apr 21 '22 at 00:52

How to add additional XML node on top of my SQL generated XML

1 Answers1