SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FOR XML - Remove attributes from FOR XML EXPLICIT


FOR XML - Remove attributes from FOR XML EXPLICIT

Author
Message
Rob
Rob
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 181
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

Thom A
Thom A
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92009 Visits: 23144
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Rob
Rob
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 181
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 :-)

Vivek Grover
Vivek Grover
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1375 Visits: 683
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




=============================================
Best Regards,
Vivek Grover
CodePicks - https://codepicks.wordpress.com//

Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172337 Visits: 24467
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?
Cool

Rob
Rob
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 181
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?
Cool


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.
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172337 Visits: 24467
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?
Cool


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

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

Rob
Rob
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 181
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.
Cool

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

Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172337 Visits: 24467
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.
Cool

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?
Cool

Mark Cowne
Mark Cowne
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25212 Visits: 27588
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.
Cool

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




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search