FOR XML - Remove attributes from FOR XML EXPLICIT
-
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
-
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 - Thom A - Monday, February 11, 2019 4:27 AM
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 🙂
- r.gall - Monday, February 11, 2019 4:11 AM
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
,NULLUNION ALL
SELECT 3 AS [Tag]
,2 AS [Parent]
,NULL
,NULL
,NULLFOR XML Explicit
Regards
VG - r.gall - Monday, February 11, 2019 4:11 AM
Quick question, why are you using FOR XML EXPLICIT?
😎 - Eirikur Eiriksson - Monday, February 11, 2019 6:03 AM
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.
- r.gall - Monday, February 11, 2019 6:23 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?
- Eirikur Eiriksson - Monday, February 11, 2019 6:43 AM
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'. - r.gall - Monday, February 11, 2019 8:15 AM
Is this the right understanding of the structure?
😎
- r.gall - Monday, February 11, 2019 8:15 AM
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
- Eirikur Eiriksson - Monday, February 11, 2019 10:28 AMr.gall - Monday, February 11, 2019 8:15 AM
Is this the right understanding of the structure?
😎Yes that is the structure 🙂
-
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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply