Home Forums SQL Server 2014 Development - SQL Server 2014 FOR XML - Remove attributes from FOR XML EXPLICIT RE: FOR XML - Remove attributes from FOR XML EXPLICIT
February 11, 2019 at 10:28 am
r.gall - Monday, February 11, 2019 8:15 AMEirikur Eiriksson - Monday, February 11, 2019 6:43 AMSuggest 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?
😎