Post reply

FOR XML - Remove attributes from FOR XML EXPLICIT

  • February 11, 2019 at 4:11 am

    #382708

    My SQL outputs the following XML with attributes:

    <Order_Notification Level="x">
    <Order_Company Level="y2">
      <Order Level="z3" />
    </Order_Company>
    </Order_Notification>

    However I need to output it without any attributes like so:

    <Order_Notification>
    <Order_Company>
      <Order>
    </Order_Company>
    </Order_Notification>

    How can I modify the SQL below to output XML without any attributes?

    SELECT    1 AS [Tag]
            ,NULL AS [Parent]
            ,'x' AS [Order_Notification!1!Level]
            ,NULL AS [Order_Company!2!Level]
            ,NULL AS [Order!3!Level]

    UNION ALL

    SELECT    2 AS [Tag]
            ,1 AS [Parent]
            ,'y1'
            ,'y2'
            ,'y3'

    UNION ALL

    SELECT    3 AS [Tag]
            ,2 AS [Parent]
            ,'z1'
            ,'z2'
            ,'z3'

    FOR XML Explicit

  • February 11, 2019 at 4:27 am

    #2021627

    That second XML isn't valid. The tag Order isn't closed. Sounds like, however, you need to pass all the values as an empty string ('')?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • February 11, 2019 at 4:53 am

    #2021630

    Thom A - Monday, February 11, 2019 4:27 AM

    That second XML isn't valid. The tag Order isn't closed. Sounds like, however, you need to pass all the values as an empty string ('')?

    You're right - I missed the '/' at the end when I keyed it. Empty strings still produced the attributes tags in the resulting XML. I figured it out though - I needed to use NULL instead 🙂

  • February 11, 2019 at 4:54 am

    #2021632

    r.gall - Monday, February 11, 2019 4:11 AM

    My SQL outputs the following XML with attributes:

    <Order_Notification Level="x">
    <Order_Company Level="y2">
      <Order Level="z3" />
    </Order_Company>
    </Order_Notification>

    However I need to output it without any attributes like so:

    <Order_Notification>
    <Order_Company>
      <Order>
    </Order_Company>
    </Order_Notification>

    How can I modify the SQL below to output XML without any attributes?

    SELECT    1 AS [Tag]
            ,NULL AS [Parent]
            ,'x' AS [Order_Notification!1!Level]
            ,NULL AS [Order_Company!2!Level]
            ,NULL AS [Order!3!Level]

    UNION ALL

    SELECT    2 AS [Tag]
            ,1 AS [Parent]
            ,'y1'
            ,'y2'
            ,'y3'

    UNION ALL

    SELECT    3 AS [Tag]
            ,2 AS [Parent]
            ,'z1'
            ,'z2'
            ,'z3'

    FOR XML Explicit

    Since, the full context that why you need that output is not known. Even then, you can try the following code:


    SELECT  1 AS [Tag]
       ,NULL AS [Parent]
       ,NULL AS [Order_Notification!1!Level]
       ,NULL AS [Order_Company!2!Level]
       ,NULL AS [Order!3!Level]

    UNION ALL

    SELECT  2 AS [Tag]
       ,1 AS [Parent]
       ,NULL
       ,NULL
       ,NULL

    UNION ALL

    SELECT  3 AS [Tag]
       ,2 AS [Parent]
       ,NULL
       ,NULL
       ,NULL

    FOR XML Explicit

    Regards
    VG

  • February 11, 2019 at 6:03 am

    #2021637

    r.gall - Monday, February 11, 2019 4:11 AM

    My SQL outputs the following XML with attributes:

    <Order_Notification Level="x">
    <Order_Company Level="y2">
      <Order Level="z3" />
    </Order_Company>
    </Order_Notification>

    However I need to output it without any attributes like so:

    <Order_Notification>
    <Order_Company>
      <Order>
    </Order_Company>
    </Order_Notification>

    How can I modify the SQL below to output XML without any attributes?

    SELECT    1 AS [Tag]
            ,NULL AS [Parent]
            ,'x' AS [Order_Notification!1!Level]
            ,NULL AS [Order_Company!2!Level]
            ,NULL AS [Order!3!Level]

    UNION ALL

    SELECT    2 AS [Tag]
            ,1 AS [Parent]
            ,'y1'
            ,'y2'
            ,'y3'

    UNION ALL

    SELECT    3 AS [Tag]
            ,2 AS [Parent]
            ,'z1'
            ,'z2'
            ,'z3'

    FOR XML Explicit

    Quick question, why are you using FOR XML EXPLICIT?
    😎

  • February 11, 2019 at 6:23 am

    #2021640

    Eirikur Eiriksson - Monday, February 11, 2019 6:03 AM

    r.gall - Monday, February 11, 2019 4:11 AM

    My SQL outputs the following XML with attributes:

    <Order_Notification Level="x">
    <Order_Company Level="y2">
      <Order Level="z3" />
    </Order_Company>
    </Order_Notification>

    However I need to output it without any attributes like so:

    <Order_Notification>
    <Order_Company>
      <Order>
    </Order_Company>
    </Order_Notification>

    How can I modify the SQL below to output XML without any attributes?

    SELECT    1 AS [Tag]
            ,NULL AS [Parent]
            ,'x' AS [Order_Notification!1!Level]
            ,NULL AS [Order_Company!2!Level]
            ,NULL AS [Order!3!Level]

    UNION ALL

    SELECT    2 AS [Tag]
            ,1 AS [Parent]
            ,'y1'
            ,'y2'
            ,'y3'

    UNION ALL

    SELECT    3 AS [Tag]
            ,2 AS [Parent]
            ,'z1'
            ,'z2'
            ,'z3'

    FOR XML Explicit

    Quick question, why are you using FOR XML EXPLICIT?
    😎

    I am using the explicit because it appears to give me more control over the XML I am generating. I have only posted a simple example here to get started, but  ultimately I will be generating up to 5 nested elements in XML from only 3 tables.

  • February 11, 2019 at 6:43 am

    #2021644

    r.gall - Monday, February 11, 2019 6:23 AM

    Eirikur Eiriksson - Monday, February 11, 2019 6:03 AM

    r.gall - Monday, February 11, 2019 4:11 AM

    My SQL outputs the following XML with attributes:

    <Order_Notification Level="x">
    <Order_Company Level="y2">
      <Order Level="z3" />
    </Order_Company>
    </Order_Notification>

    However I need to output it without any attributes like so:

    <Order_Notification>
    <Order_Company>
      <Order>
    </Order_Company>
    </Order_Notification>

    How can I modify the SQL below to output XML without any attributes?

    SELECT    1 AS [Tag]
            ,NULL AS [Parent]
            ,'x' AS [Order_Notification!1!Level]
            ,NULL AS [Order_Company!2!Level]
            ,NULL AS [Order!3!Level]

    UNION ALL

    SELECT    2 AS [Tag]
            ,1 AS [Parent]
            ,'y1'
            ,'y2'
            ,'y3'

    UNION ALL

    SELECT    3 AS [Tag]
            ,2 AS [Parent]
            ,'z1'
            ,'z2'
            ,'z3'

    FOR XML Explicit

    Quick question, why are you using FOR XML EXPLICIT?
    😎

    I am using the explicit because it appears to give me more control over the XML I am generating. I have only posted a simple example here to get started, but  ultimately I will be generating up to 5 nested elements in XML from only 3 tables.

    Suggest you look into FOR XML PATH, easier to use and provides more control over the output.
    😎

    Can you post the DDL (create table) scripts, sample data as insert statement and the expected results please?

  • February 11, 2019 at 8:15 am

    #2021659

    Eirikur Eiriksson - Monday, February 11, 2019 6:43 AM

    Suggest you look into FOR XML PATH, easier to use and provides more control over the output.
    😎

    Can you post the DDL (create table) scripts, sample data as insert statement and the expected results please?

    It's a shopping cart that gets converted to XML. Here is the code to create test data in a series of table 4 variables - The 'shopping cart header', and 'shopping cart order line' tables, and then 2 tables which contain the 'products' and the 'modules' they belong to.

    -- table: shopping cart header
    DECLARE @ShoppingCartHeader TABLE
    (
        fldShoppingCartHeaderID bigint
        ,fldDeliveryFirstName varchar(100)
        ,fldDeliveryLastName varchar(100)
        ,fldDeliveryEmail varchar(255)
        ,fldOrderNumber varchar(100)
    )
    INSERT INTO @ShoppingCartHeader VALUES(1,'Joe','Bloggs','j.bloggs@example.com','ABCDEFG')
    INSERT INTO @ShoppingCartHeader VALUES(2,'Clare','Dune','clare146@example.com','XYZXYZ')

    SELECT * FROM @ShoppingCartHeader

    -- table: shopping cart contents
    DECLARE @ShoppingCartContents TABLE
    (
        fldShoppingBasketLineID bigint
        ,fldShoppingCartHeaderID bigint
        ,fldProductID bigint
        ,fldUnitPrice money
        ,fldQuantityOrdered int
        ,fldOption varchar(100)
    )
    INSERT INTO @ShoppingCartContents VALUES(1,1,100,1.20,1,'bound')
    INSERT INTO @ShoppingCartContents VALUES(2,1,101,1.50,2,'unbound')
    INSERT INTO @ShoppingCartContents VALUES(3,2,100,1.20,5,'bound')

    SELECT * FROM @ShoppingCartContents

    -- table: course_module
    DECLARE @Module TABLE
    (
        fldModuleID bigint
        ,fldModuleTitle varchar(100)
    )
    INSERT INTO @Module VALUES (1,'Ecosystems')
    INSERT INTO @Module VALUES (2,'Welfare')

    SELECT * FROM @Module

    -- table: products
    DECLARE @Products TABLE
    (
        fldProductID bigint
        ,fldProductCode varchar(100)
        ,fldProductTitle varchar(100)
        ,fldModuleID bigint
    )
    INSERT INTO @Products VALUES (100,'XFN039080','Block A: Chapters 1-3',1)
    INSERT INTO @Products VALUES (101,'XFN039081','Block A: Chapters 4-10',1)
    INSERT INTO @Products VALUES (102,'JNN044722','Block H: Chapters 1-22',2)

    SELECT * FROM @Products

    The  required XML should look like this (only  XML for 1 order needs to be generated at a time, so I will be passing in the value of '2' for fldShoppingCartHeaderID, and expect the following XML output:

    <Order_Notification>
        <Order_Company Source="web">
            <Order OrderNumber="ABCDEFG">
                <OrderCustomer FirstName="Joe" LastName="Bloggs" Email="j.bloggs@example.com"/>
                <OrderLineItems>
                    <OrderLineItem ModultTitle="Ecosystems">
                        <Products>
                            <Product ProductCode="XFN039080" Option="bound" Quantity="1" UnitPrice="1.20" TotalPrice="1.20" />
                            <Product ProductCode="XFN039081" Option="unbound" Quantity="2" UnitPrice="1.50" TotalPrice="3.00" />
                        </Products>
                    </OrderLineItem>
                </OrderLineItems>
            </Order>
        </Order_Company>
    </Order_Notification>
       
    The attribute Source="web" in the <Order_Comapny> element is not data driven, but will be a static value always set to 'web'.

  • February 11, 2019 at 10:28 am

    #2021678

    r.gall - Monday, February 11, 2019 8:15 AM

    Eirikur Eiriksson - Monday, February 11, 2019 6:43 AM

    Suggest you look into FOR XML PATH, easier to use and provides more control over the output.
    😎

    Can you post the DDL (create table) scripts, sample data as insert statement and the expected results please?

    It's a shopping cart that gets converted to XML. Here is the code to create test data in a series of table 4 variables - The 'shopping cart header', and 'shopping cart order line' tables, and then 2 tables which contain the 'products' and the 'modules' they belong to.

    -- table: shopping cart header
    DECLARE @ShoppingCartHeader TABLE
    (
        fldShoppingCartHeaderID bigint
        ,fldDeliveryFirstName varchar(100)
        ,fldDeliveryLastName varchar(100)
        ,fldDeliveryEmail varchar(255)
        ,fldOrderNumber varchar(100)
    )
    INSERT INTO @ShoppingCartHeader VALUES(1,'Joe','Bloggs','j.bloggs@example.com','ABCDEFG')
    INSERT INTO @ShoppingCartHeader VALUES(2,'Clare','Dune','clare146@example.com','XYZXYZ')

    SELECT * FROM @ShoppingCartHeader

    -- table: shopping cart contents
    DECLARE @ShoppingCartContents TABLE
    (
        fldShoppingBasketLineID bigint
        ,fldShoppingCartHeaderID bigint
        ,fldProductID bigint
        ,fldUnitPrice money
        ,fldQuantityOrdered int
        ,fldOption varchar(100)
    )
    INSERT INTO @ShoppingCartContents VALUES(1,1,100,1.20,1,'bound')
    INSERT INTO @ShoppingCartContents VALUES(2,1,101,1.50,2,'unbound')
    INSERT INTO @ShoppingCartContents VALUES(3,2,100,1.20,5,'bound')

    SELECT * FROM @ShoppingCartContents

    -- table: course_module
    DECLARE @Module TABLE
    (
        fldModuleID bigint
        ,fldModuleTitle varchar(100)
    )
    INSERT INTO @Module VALUES (1,'Ecosystems')
    INSERT INTO @Module VALUES (2,'Welfare')

    SELECT * FROM @Module

    -- table: products
    DECLARE @Products TABLE
    (
        fldProductID bigint
        ,fldProductCode varchar(100)
        ,fldProductTitle varchar(100)
        ,fldModuleID bigint
    )
    INSERT INTO @Products VALUES (100,'XFN039080','Block A: Chapters 1-3',1)
    INSERT INTO @Products VALUES (101,'XFN039081','Block A: Chapters 4-10',1)
    INSERT INTO @Products VALUES (102,'JNN044722','Block H: Chapters 1-22',2)

    SELECT * FROM @Products

    The  required XML should look like this (only  XML for 1 order needs to be generated at a time, so I will be passing in the value of '2' for fldShoppingCartHeaderID, and expect the following XML output:

    <Order_Notification>
        <Order_Company Source="web">
            <Order OrderNumber="ABCDEFG">
                <OrderCustomer FirstName="Joe" LastName="Bloggs" Email="j.bloggs@example.com"/>
                <OrderLineItems>
                    <OrderLineItem ModultTitle="Ecosystems">
                        <Products>
                            <Product ProductCode="XFN039080" Option="bound" Quantity="1" UnitPrice="1.20" TotalPrice="1.20" />
                            <Product ProductCode="XFN039081" Option="unbound" Quantity="2" UnitPrice="1.50" TotalPrice="3.00" />
                        </Products>
                    </OrderLineItem>
                </OrderLineItems>
            </Order>
        </Order_Company>
    </Order_Notification>
       
    The attribute Source="web" in the <Order_Comapny> element is not data driven, but will be a static value always set to 'web'.

    Is this the right understanding of the structure?
    😎

  • February 11, 2019 at 1:10 pm

    #2021704

    r.gall - Monday, February 11, 2019 8:15 AM

    Eirikur Eiriksson - Monday, February 11, 2019 6:43 AM

    Suggest you look into FOR XML PATH, easier to use and provides more control over the output.
    😎

    Can you post the DDL (create table) scripts, sample data as insert statement and the expected results please?

    It's a shopping cart that gets converted to XML. Here is the code to create test data in a series of table 4 variables - The 'shopping cart header', and 'shopping cart order line' tables, and then 2 tables which contain the 'products' and the 'modules' they belong to.

    -- table: shopping cart header
    DECLARE @ShoppingCartHeader TABLE
    (
        fldShoppingCartHeaderID bigint
        ,fldDeliveryFirstName varchar(100)
        ,fldDeliveryLastName varchar(100)
        ,fldDeliveryEmail varchar(255)
        ,fldOrderNumber varchar(100)
    )
    INSERT INTO @ShoppingCartHeader VALUES(1,'Joe','Bloggs','j.bloggs@example.com','ABCDEFG')
    INSERT INTO @ShoppingCartHeader VALUES(2,'Clare','Dune','clare146@example.com','XYZXYZ')

    SELECT * FROM @ShoppingCartHeader

    -- table: shopping cart contents
    DECLARE @ShoppingCartContents TABLE
    (
        fldShoppingBasketLineID bigint
        ,fldShoppingCartHeaderID bigint
        ,fldProductID bigint
        ,fldUnitPrice money
        ,fldQuantityOrdered int
        ,fldOption varchar(100)
    )
    INSERT INTO @ShoppingCartContents VALUES(1,1,100,1.20,1,'bound')
    INSERT INTO @ShoppingCartContents VALUES(2,1,101,1.50,2,'unbound')
    INSERT INTO @ShoppingCartContents VALUES(3,2,100,1.20,5,'bound')

    SELECT * FROM @ShoppingCartContents

    -- table: course_module
    DECLARE @Module TABLE
    (
        fldModuleID bigint
        ,fldModuleTitle varchar(100)
    )
    INSERT INTO @Module VALUES (1,'Ecosystems')
    INSERT INTO @Module VALUES (2,'Welfare')

    SELECT * FROM @Module

    -- table: products
    DECLARE @Products TABLE
    (
        fldProductID bigint
        ,fldProductCode varchar(100)
        ,fldProductTitle varchar(100)
        ,fldModuleID bigint
    )
    INSERT INTO @Products VALUES (100,'XFN039080','Block A: Chapters 1-3',1)
    INSERT INTO @Products VALUES (101,'XFN039081','Block A: Chapters 4-10',1)
    INSERT INTO @Products VALUES (102,'JNN044722','Block H: Chapters 1-22',2)

    SELECT * FROM @Products

    The  required XML should look like this (only  XML for 1 order needs to be generated at a time, so I will be passing in the value of '2' for fldShoppingCartHeaderID, and expect the following XML output:

    <Order_Notification>
        <Order_Company Source="web">
            <Order OrderNumber="ABCDEFG">
                <OrderCustomer FirstName="Joe" LastName="Bloggs" Email="j.bloggs@example.com"/>
                <OrderLineItems>
                    <OrderLineItem ModultTitle="Ecosystems">
                        <Products>
                            <Product ProductCode="XFN039080" Option="bound" Quantity="1" UnitPrice="1.20" TotalPrice="1.20" />
                            <Product ProductCode="XFN039081" Option="unbound" Quantity="2" UnitPrice="1.50" TotalPrice="3.00" />
                        </Products>
                    </OrderLineItem>
                </OrderLineItems>
            </Order>
        </Order_Company>
    </Order_Notification>
       
    The attribute Source="web" in the <Order_Comapny> element is not data driven, but will be a static value always set to 'web'.

    If I understand your data correctly, this should work

    SELECT 'web' AS "@Source",
      (
         SELECT fldOrderNumber AS "@OrderNumber",
           fldDeliveryFirstName AS "OrderCustomer/@FirstName",
           fldDeliveryLastName AS "OrderCustomer/@LastName",
           fldDeliveryEmail AS "OrderCustomer/@Email",
                 (
                 SELECT m.fldModuleTitle AS "@ModultTitle",
                     (
                        SELECT p.fldProductCode AS "@ProductCode",
                             c.fldOption AS "@Option",
                             c.fldQuantityOrdered AS "@Quantity",
                             c.fldUnitPrice AS "@UnitPrice",
                             c.fldUnitPrice * c.fldQuantityOrdered AS "@TotalPrice"
                        FROM @ShoppingCartContents c
                        INNER JOIN @Products p ON p.fldProductID = c.fldProductID
                        WHERE c.fldShoppingCartHeaderID = h.fldShoppingCartHeaderID
                         AND m.fldModuleID = p.fldModuleID
                        FOR XML PATH('Products'),ROOT('Product'),TYPE
                     )
                 FROM @Module m
                 WHERE EXISTS(SELECT * FROM @ShoppingCartContents c
                        INNER JOIN @Products p ON p.fldProductID = c.fldProductID
                             WHERE c.fldShoppingCartHeaderID = h.fldShoppingCartHeaderID
                              AND m.fldModuleID = p.fldModuleID)
                 FOR XML PATH('OrderLineItem'),ROOT('OrderLineItems'),TYPE
                 )
         FROM @ShoppingCartHeader h
         FOR XML PATH('Order'),TYPE
         )
    FOR XML PATH('Order_Company'),ROOT('Order_Notification'),TYPE;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 12, 2019 at 2:05 am

    #2021753

    Eirikur Eiriksson - Monday, February 11, 2019 10:28 AM

    r.gall - Monday, February 11, 2019 8:15 AM

    Eirikur Eiriksson - Monday, February 11, 2019 6:43 AM

    Suggest you look into FOR XML PATH, easier to use and provides more control over the output.
    😎

    Can you post the DDL (create table) scripts, sample data as insert statement and the expected results please?

    It's a shopping cart that gets converted to XML. Here is the code to create test data in a series of table 4 variables - The 'shopping cart header', and 'shopping cart order line' tables, and then 2 tables which contain the 'products' and the 'modules' they belong to.

    -- table: shopping cart header
    DECLARE @ShoppingCartHeader TABLE
    (
        fldShoppingCartHeaderID bigint
        ,fldDeliveryFirstName varchar(100)
        ,fldDeliveryLastName varchar(100)
        ,fldDeliveryEmail varchar(255)
        ,fldOrderNumber varchar(100)
    )
    INSERT INTO @ShoppingCartHeader VALUES(1,'Joe','Bloggs','j.bloggs@example.com','ABCDEFG')
    INSERT INTO @ShoppingCartHeader VALUES(2,'Clare','Dune','clare146@example.com','XYZXYZ')

    SELECT * FROM @ShoppingCartHeader

    -- table: shopping cart contents
    DECLARE @ShoppingCartContents TABLE
    (
        fldShoppingBasketLineID bigint
        ,fldShoppingCartHeaderID bigint
        ,fldProductID bigint
        ,fldUnitPrice money
        ,fldQuantityOrdered int
        ,fldOption varchar(100)
    )
    INSERT INTO @ShoppingCartContents VALUES(1,1,100,1.20,1,'bound')
    INSERT INTO @ShoppingCartContents VALUES(2,1,101,1.50,2,'unbound')
    INSERT INTO @ShoppingCartContents VALUES(3,2,100,1.20,5,'bound')

    SELECT * FROM @ShoppingCartContents

    -- table: course_module
    DECLARE @Module TABLE
    (
        fldModuleID bigint
        ,fldModuleTitle varchar(100)
    )
    INSERT INTO @Module VALUES (1,'Ecosystems')
    INSERT INTO @Module VALUES (2,'Welfare')

    SELECT * FROM @Module

    -- table: products
    DECLARE @Products TABLE
    (
        fldProductID bigint
        ,fldProductCode varchar(100)
        ,fldProductTitle varchar(100)
        ,fldModuleID bigint
    )
    INSERT INTO @Products VALUES (100,'XFN039080','Block A: Chapters 1-3',1)
    INSERT INTO @Products VALUES (101,'XFN039081','Block A: Chapters 4-10',1)
    INSERT INTO @Products VALUES (102,'JNN044722','Block H: Chapters 1-22',2)

    SELECT * FROM @Products

    The  required XML should look like this (only  XML for 1 order needs to be generated at a time, so I will be passing in the value of '2' for fldShoppingCartHeaderID, and expect the following XML output:

    <Order_Notification>
        <Order_Company Source="web">
            <Order OrderNumber="ABCDEFG">
                <OrderCustomer FirstName="Joe" LastName="Bloggs" Email="j.bloggs@example.com"/>
                <OrderLineItems>
                    <OrderLineItem ModultTitle="Ecosystems">
                        <Products>
                            <Product ProductCode="XFN039080" Option="bound" Quantity="1" UnitPrice="1.20" TotalPrice="1.20" />
                            <Product ProductCode="XFN039081" Option="unbound" Quantity="2" UnitPrice="1.50" TotalPrice="3.00" />
                        </Products>
                    </OrderLineItem>
                </OrderLineItems>
            </Order>
        </Order_Company>
    </Order_Notification>
       
    The attribute Source="web" in the <Order_Comapny> element is not data driven, but will be a static value always set to 'web'.

    Is this the right understanding of the structure?
    😎

    Yes that is the structure 🙂

  • February 12, 2019 at 4:12 am

    #2021755

    Here is one way of doing this, the code will either produce multiple sets or a single set if filtered.
    😎


    SELECT
      'web' AS 'Order_Company/@Source'
     ,(
       SELECT
        SSCH.fldOrderNumber AS '@OrderNumber'
        ,(
          SELECT
           XSCH.fldDeliveryFirstName AS '@FirstName'
           ,XSCH.fldDeliveryLastName AS '@LastName'
           ,XSCH.fldDeliveryEmail  AS '@Email'
          FROM  @ShoppingCartHeader XSCH
          WHERE XSCH.fldShoppingCartHeaderID  = SSCH.fldShoppingCartHeaderID
          FOR XML PATH('OrderCustomer'), TYPE
        )
        ,(
          SELECT TOP (1)
           MDL.fldModuleTitle AS '@ModultTitle'
           ,(
            SELECT
              XPRD.fldProductCode         AS '@ProductCode'
             ,XSCC.fldOption           AS '@Option'
             ,XSCC.fldQuantityOrdered        AS '@Quantity'
             ,XSCC.fldUnitPrice          AS '@UnitPrice'
             ,(XSCC.fldQuantityOrdered * XSCC.fldUnitPrice) AS '@TotalPrice'
            FROM @Products XPRD
            INNER JOIN @ShoppingCartContents  XSCC
            ON   XPRD.fldProductID   = XSCC.fldProductID
            WHERE XSCC.fldShoppingCartHeaderID = SCC.fldShoppingCartHeaderID
            FOR XML PATH('Product'),ROOT('Products'),TYPE
           )
          FROM @ShoppingCartContents SCC
          INNER JOIN @Products   PRD
          ON    SCC.fldProductID  = PRD.fldProductID
          INNER JOIN @Module      MDL
          ON   PRD.fldModuleID  = MDL.fldModuleID
          WHERE   SCC.fldShoppingCartHeaderID = SSCH.fldShoppingCartHeaderID
          FOR XML PATH('OrderLineItem'), ROOT('OrderLineItems'),TYPE
        )
       FROM @ShoppingCartHeader  SSCH
       WHERE SSCH.fldShoppingCartHeaderID  = SCH.fldShoppingCartHeaderID
       FOR XML PATH('Order'),TYPE
      )
    FROM  @ShoppingCartHeader SCH
    FOR XML PATH ('Order_Notification'), TYPE;

    Multi-set output

    <Order_Notification>
    <Order_Company Source="web" />
    <Order OrderNumber="ABCDEFG">
      <OrderCustomer FirstName="Joe" LastName="Bloggs" Email="j.bloggs@example.com" />
      <OrderLineItems>
      <OrderLineItem ModultTitle="Ecosystems">
       <Products>
        <Product ProductCode="XFN039080" Option="bound" Quantity="1" UnitPrice="1.2000" TotalPrice="1.2000" />
        <Product ProductCode="XFN039081" Option="unbound" Quantity="2" UnitPrice="1.5000" TotalPrice="3.0000" />
       </Products>
      </OrderLineItem>
      </OrderLineItems>
    </Order>
    </Order_Notification>
    <Order_Notification>
    <Order_Company Source="web" />
    <Order OrderNumber="XYZXYZ">
      <OrderCustomer FirstName="Clare" LastName="Dune" Email="clare146@example.com" />
      <OrderLineItems>
      <OrderLineItem ModultTitle="Ecosystems">
       <Products>
        <Product ProductCode="XFN039080" Option="bound" Quantity="5" UnitPrice="1.2000" TotalPrice="6.0000" />
       </Products>
      </OrderLineItem>
      </OrderLineItems>
    </Order>
    </Order_Notification>

    Edit: Corrected missing attribute alias.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy