locked
Need assistance on SQL FOR XML PATH RRS feed

  • Question


  • Hi
    I would really appreciate  some assistance. I'm using the SQL FOR XML PATH but I have some issues with the nodes. 

    I have a simple table (below is the script for generating)


    voucher_number payment_number payment_date
    ---------------------------------------------------------------------------------
    D15 C15 2020-07-27
    D16 C16 2020-07-25


    I would like the XML to look like this, look at the node of <object>:

    <?xml version="1.0" encoding="UTF-8" >
    <objects>
    <object>
    <record>
    <voucher_number>D15</voucher_number>
    <payment_number>C15</payment_number>
    <payment_date>2020-07-27</payment_date>
    </record>
    <record>
    <voucher_number>D16</voucher_number>
    <payment_number>C16</payment_number>
    <payment_date>2020-07-25</payment_date>
    </record>
    </object>
    </objects>

    But when I run this script

    SELECT 
          [voucher_number] as [record/voucher_number]
          ,[payment_number] as [record/payment_number]
          ,[payment_date] as [record/payment_date]
      FROM #t1
    FOR XML PATH ('object'), ROOT('Objects')


    it looks like this.... look at the level of "object".


    <?xml version="1.0" encoding="UTF-8"?>
    <Objects>
       <object>
          <record>
             <voucher_number>D15</voucher_number>
             <payment_number>C15</payment_number>
             <payment_date>2020-07-27</payment_date>
          </record>
       </object>
       <object>
          <record>
             <voucher_number>D16</voucher_number>
             <payment_number>C16</payment_number>
             <payment_date>2020-07-25</payment_date>
          </record>
       </object>
    </Objects>


    How should I change the script to make the <object> to spann the two <record>?

    Many, many thanks in advance :)


    --- Script to generate the test table ---
    Create table #t1 
    (
    [voucher_number] [nvarchar](50) NULL,
    [payment_number] [nvarchar](50) NULL,
    [payment_date] [date] NULL
    )

    INSERT #t1 ([voucher_number], [payment_number], [payment_date]) VALUES (N'D15', N'C15',  CAST(N'2020-07-27' AS Date))
    GO
    INSERT #t1 ([voucher_number], [payment_number], [payment_date]) VALUES (N'D16', N'C16', CAST(N'2020-07-25' AS Date))


    Tuesday, August 4, 2020 10:07 AM

All replies

  • Nested XML, requires nested queries. E.g.

    DECLARE @t1 TABLE (
        voucher_number NVARCHAR(50) NULL ,
        payment_number NVARCHAR(50) NULL ,
        payment_date DATE NULL
    );
    
    INSERT INTO @t1 ( voucher_number ,
                      payment_number ,
                      payment_date )
    VALUES ( N'D15', N'C15', CAST(N'2020-07-27' AS DATE)) ,
           ( N'D16', N'C16', CAST(N'2020-07-25' AS DATE));
    
    SELECT (   SELECT voucher_number AS [voucher_number] ,
                      payment_number AS [payment_number] ,
                      payment_date AS [payment_date]
               FROM   @t1
               FOR XML PATH('record'), TYPE ) AS [object]
    FOR XML PATH('objects');

    • Proposed as answer by Naomi N Tuesday, August 4, 2020 1:55 PM
    Tuesday, August 4, 2020 10:31 AM
  • Microsoft Outlook Customer Service | Outlook Helpline Support – 24×7 Toll Free Number Call 1-800-581-0428 toll free for Microsoft related Activation issues. Note:- We are third party independent Microsoft tech support service provider.

    If you do not have an Outlook account, you do not have to worry. You can use any email account server. In this, you get a lot of advanced security which saves your email. To learn more about Outlook, you can visit Outlook Customer Service, dial Microsoft Outlook Helpline Number.

    Tuesday, August 4, 2020 10:33 AM