Nested XML SQL QUERY

Question

This is my SQL Query

SELECT
    (SELECT TOP 2 
         Docnum AS [PONUMBER],  
         (SELECT FirstName + lastName AS [CreatedBy] 
          FROM OHEM 
          WHERE empID = T0.OwnerCode) AS OwnerCode,
        T0.CardCode AS [VENDOR_NO],  
        CardName AS [VENDOR_NAME],
        T0.U_TelFax AS [TELENO], 
        T0.DocDate AS [PODATE], 
        'SPX' AS [COMPCODE],
        Comments AS [HEAD_TXT], 
        U_ETA AS [EXPDATE],  
        U_No_of_packages AS [ESTIMATEDPACKAGES],
        REPLACE(REPLACE(T0.Address2, CHAR(10), ''), CHAR(13), '') AS [STREET]  
    FROM
        OPOR T0    
    ORDER BY 
        CreateDate DESC
    FOR XML PATH('ZPOHEAD_DATA'), ROOT('POHEAD'), TYPE) POXML

This is is my output:

<POHEAD>
  <ZPOHEAD_DATA>
    <PONUMBER>25611</PONUMBER>
    <OwnerCode>CandyLai</OwnerCode>
    <VENDOR_NO>V-0410-EUR</VENDOR_NO>
    <VENDOR_NAME>Robopac S.p.A.</VENDOR_NAME>
    <PODATE>2017-10-04T00:00:00</PODATE>
    <COMPCODE>SPX</COMPCODE>
    <HEAD_TXT>Based On Purchase Request 8315. 8321.</HEAD_TXT>
    <STREET>23A Tech Park CrescentSingapore 637848</STREET>
  </ZPOHEAD_DATA>
  <ZPOHEAD_DATA>
    <PONUMBER>25612</PONUMBER>
    <OwnerCode>CandyLai</OwnerCode>
    <VENDOR_NO>V-0157-SGD</VENDOR_NO>
    <VENDOR_NAME>Eltete Malaysia Sdn Bhd</VENDOR_NAME>
    <PODATE>2017-10-04T00:00:00</PODATE>
    <COMPCODE>SPX</COMPCODE>
    <HEAD_TXT>*yb mrp</HEAD_TXT>
    <STREET>15 Joo Koon Road Singapore 628976</STREET>
  </ZPOHEAD_DATA>
</POHEAD>

But I need the output to be like this - please help me to modify the query based on the below output

 <POXML>
        <POHEAD>
            <ZPOHEAD_DATA>
                <PONUMBER>25611</PONUMBER>
                <OwnerCode>CandyLai</OwnerCode>
                <VENDOR_NO>V-0410-EUR</VENDOR_NO>
                <VENDOR_NAME>Robopac S.p.A.</VENDOR_NAME>
                <PODATE>2017-10-04T00:00:00</PODATE>
                <COMPCODE>SPX</COMPCODE>
                <HEAD_TXT>Based On Purchase Request 8315. 8321.</HEAD_TXT>
                <STREET>23A Tech Park CrescentSingapore 637848</STREET>
            </ZPOHEAD_DATA>
        </POHEAD>
        <POHEAD>
            <ZPOHEAD_DATA>
                <PONUMBER>25612</PONUMBER>
                <OwnerCode>CandyLai</OwnerCode>
                <VENDOR_NO>V-0157-SGD</VENDOR_NO>
                <VENDOR_NAME>Eltete Malaysia Sdn Bhd</VENDOR_NAME>
                <PODATE>2017-10-04T00:00:00</PODATE>
                <COMPCODE>SPX</COMPCODE>
                <HEAD_TXT>*yb mrp</HEAD_TXT>
                <STREET>15 Joo Koon Road Singapore 628976</STREET>
            </ZPOHEAD_DATA>
        </POHEAD>
    </POXML>

edit the question add some sample data.

Yogesh Sharma
– Yogesh Sharma

2017-10-17 04:34:22 +00:00
Commented Oct 17, 2017 at 4:34 — Yogesh Sharma
– Yogesh Sharma, Commented Oct 17, 2017 at 4:34

Pratik Vaghasiya · Accepted Answer · 2017-10-17 08:19:23Z

You need to put at last "FOR XML PATH('')". take this code below

      SELECT
        (SELECT TOP 2 
             Docnum AS [PONUMBER],  
             (SELECT FirstName + lastName AS [CreatedBy] 
              FROM OHEM 
              WHERE empID = T0.OwnerCode) AS OwnerCode,
            T0.CardCode AS [VENDOR_NO],  
            CardName AS [VENDOR_NAME],
            T0.U_TelFax AS [TELENO], 
            T0.DocDate AS [PODATE], 
            'SPX' AS [COMPCODE],
            Comments AS [HEAD_TXT], 
            U_ETA AS [EXPDATE],  
            U_No_of_packages AS [ESTIMATEDPACKAGES],
            REPLACE(REPLACE(T0.Address2, CHAR(10), ''), CHAR(13), '') AS [STREET]  
        FROM
            OPOR T0    
        ORDER BY 
            CreateDate DESC
        FOR XML PATH('ZPOHEAD_DATA'), ROOT('POHEAD'), TYPE) POXML FOR XML PATH('')

Collectives™ on Stack Overflow

Nested XML SQL QUERY

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related