I have generated XML from a SQL Server FOR XML PATH statement as shown here: And this is the result: Now, I'd like to encapsulate the above with these nodes: 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

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

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;

Answer

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

Advertisement

Answer