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'.