XML Output in SQL (works perfect in ORACLE)

Question

Post reply

XML Output in SQL (works perfect in ORACLE)

BOR15K

SSCertifiable

Points: 5894
More actions
July 12, 2011 at 5:29 am

#243880

Hi All,
I need your advise, please. I have to extract a very simple data from a SQL: one main table and one linked -
customer's site and its equipment. Using Oracle it was very easy to achieve what I need using XMLAGG / XMLFOREST, but I am straggling with SQL 2008 and your help will be more than appropriated, please.
(1) The outcome I need is as follow
<?xml version="1.0" encoding="ISO-8859-1" standalone="no" ?>
<ns1:site_equipment xmlns:ns1="ns0:cape_site" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<site_code>2E1012</site_code>
<erected_equipment_list>
<equipment_code>S1307</equipment_code>
<erected_qnty>3658</erected_qnty>
</erected_equipment_list>
<erected_equipment_list>
<equipment_code>S1308</equipment_code>
<erected_qnty>1685</erected_qnty>
</erected_equipment_list>
<erected_equipment_list>
<equipment_code>S1309</equipment_code>
<erected_qnty>356</erected_qnty>
</erected_equipment_list>
<erected_equipment_list>
<equipment_code>S215</equipment_code>
<erected_qnty>806</erected_qnty>
</erected_equipment_list>
</ns1:site_equipment>
(2) To achieve it I have tried so far:
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi,
'http://www.w3.org/2001/XMLSchema' AS xsd,
'ns0:cape_site' AS ns1)
SELECT jc.WBSNumber "site_code",
jst.EquipmentNo "erected_equipment_list/equipment_code",
jst.Qty "erected_equipment_list/erected_qnty"
FROM Job_Stock_Tbl jst
JOIN Job_Card jc
ON jc.CapeId = jst.CapeId
FOR XML PATH(''), ROOT ('ns1:site_equipment');
But got an output like this:
<ns1:site_equipment xmlns:ns1="ns0:cape_site" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<site_code>2E1012</site_code>
<erected_equipment_list>
<equipment_code>S1307</equipment_code>
<erected_qnty>3658</erected_qnty>
</erected_equipment_list>
<site_code>2E1012</site_code>
<erected_equipment_list>
<equipment_code>S1308</equipment_code>
<erected_qnty>1685</erected_qnty>
</erected_equipment_list>
<site_code>2E1012</site_code>
<erected_equipment_list>
<equipment_code>S1309</equipment_code>
<erected_qnty>356</erected_qnty>
</erected_equipment_list>
<site_code>2E1012</site_code>
<erected_equipment_list>
<equipment_code>S215</equipment_code>
<erected_qnty>806</erected_qnty>
</erected_equipment_list>
</ns1:site_equipment>
which has <site_code>2E1012</site_code> replicated more than once.
(3) Also have tried this, but came up with a total disaster:
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi,
'http://www.w3.org/2001/XMLSchema' AS xsd,
'ns0:cape_site' AS ns1)
SELECT jc.WBSNumber "site_code",
(SELECT
jst.EquipmentNo "equipment_code",
SUM(jst.Qty) "erected_qnty"
FROM Job_Stock_Tbl jst
WHERE jc.CapeId = jst.CapeId
FOR XML PATH('erected_equipment_list'),TYPE)
FROM Job_Card jc
--group by jc.WBSNumber
FOR XML PATH(''), ROOT ('ns1:site_equipment');
Please advise.
Thanks in advance!

Viewing 15 posts - 1 through 14 (of 14 total)

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Can you post the DDL and DML to create the sample data.

____________________________________________________

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

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

Apologies,

please find below:

CREATE TABLE Job_Stock_Tbl

(

CapeId INT NULL,

EquipmentNo VARCHAR(15) NULL,

Qty INT NULL

);

GO

CREATE TABLE Job_Card

(

CapeId INT NOT NULL,

WBSNumber VARCHAR(15) NULL

);

GO

INSERT INTO Job_Card

VALUES (1,'2E1012');

INSERT INTO Job_Stock_Tbl

VALUES (1,'S1307',2);

INSERT INTO Job_Stock_Tbl

VALUES (1,'S1308',3);

INSERT INTO Job_Stock_Tbl

VALUES (1,'S1309',5);

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

I'm not sure if you can do this without generating extra namespace definitions, there's a Connect item for it here

http://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi,

'http://www.w3.org/2001/XMLSchema' AS xsd,

'ns0:cape_site' AS ns1)

SELECT jc.WBSNumber AS "site_code",

(SELECT jst.EquipmentNo "equipment_code",

jst.Qty "erected_qnty"

FROM Job_Stock_Tbl jst

WHERE jc.CapeId = jst.CapeId

FOR XML PATH('erected_equipment_list'),TYPE

)

FROM Job_Card jc

FOR XML PATH('ns1:site_equipment'),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

BOR15K SSCertifiable Points: 5894 More actions · Answer 4

Let's omit XMLNAMESPACES for a moment,

How would you update my code to extract the site_code element only once, please?

Thanks

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

This is the query from above without the WITH XMLNAMESPACES clause

SELECT jc.WBSNumber AS "site_code",

(SELECT jst.EquipmentNo "equipment_code",

jst.Qty "erected_qnty"

FROM Job_Stock_Tbl jst

WHERE jc.CapeId = jst.CapeId

FOR XML PATH('erected_equipment_list'),TYPE

)

FROM Job_Card jc

FOR XML PATH('site_equipment'),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

BOR15K SSCertifiable Points: 5894 More actions · Answer 6

BOR15K

SSCertifiable

Points: 5894

July 12, 2011 at 8:09 am

#1351596

Thanks

BOR15K SSCertifiable Points: 5894 More actions · Answer 7

SOLVED IT!

THANKS FOR THE IDEAS!

DECLARE @xml_body xml;

SET @xml_body = (

SELECT jst.EquipmentNo "erected_equipment_list/equipment_code",

SUM(jst.Qty) "erected_equipment_list/erected_qnty"

FROM Job_Stock_Tbl jst

JOIN Job_Card job

ON job.CapeId = jst.CapeId

WHERE jst.EquipmentNo IN ('S215','S1307','S1308','S1309')

and job.WBSNumber = '2E1012'

GROUP BY job.WBSNumber,

jst.EquipmentNo

FOR XML PATH(''),type);

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi,

'http://www.w3.org/2001/XMLSchema' AS xsd,

'ns0:cape_site' AS ns1)

SELECT '2E1012' "site_code",

@xml_body

FOR XML PATH(''),ROOT ('ns1:site_equipment');

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 8

Great, here's another way

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi,

'http://www.w3.org/2001/XMLSchema' AS xsd,

'ns0:cape_site' AS ns1),

OrderedData AS (

SELECT jc.WBSNumber,

jst.EquipmentNo,

jst.Qty,

ROW_NUMBER() OVER(PARTITION BY jc.WBSNumber ORDER BY jst.EquipmentNo) AS rn

FROM Job_Card jc

INNER JOIN Job_Stock_Tbl jst ON jc.CapeId = jst.CapeId

)

SELECT CASE WHEN rn=1 THEN WBSNumber END AS "site_code",

EquipmentNo AS "erected_equipment_list/equipment_code",

Qty AS "erected_equipment_list/erected_qnty"

FROM OrderedData

FOR XML PATH(''),ROOT('ns1:site_equipment'),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

BOR15K SSCertifiable Points: 5894 More actions · Answer 9

OK,

First you won - yours looks better 😀 . Thx

Second - I was too excited: How do I add <?xml version="1.0" encoding="ISO-8859-1" standalone="no" ?>

for the happy end?

THX!

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 10

As far as I know, the only workaround is to convert the XML to the varchar(max) type and then prepend the header

SELECT '<?xml version="1.0" encoding="ISO-8859-1" ?>'

+

CAST( (SELECT ... FOR XML PATH...) ) AS VARCHAR(MAX))

____________________________________________________

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

BOR15K SSCertifiable Points: 5894 More actions · Answer 11

This is what I thought as well, but didn't want to convert it to VARCHAR.

Again, thanks for the help.

BOR15K SSCertifiable Points: 5894 More actions · Answer 12

Mark,

now to the most stupid question, please:

How do I assign the result to a variable, i.e. how do I write something like this that will also work 😀

DECLARE @xml_result xml;

@xml_result = WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi,

'http://www.w3.org/2001/XMLSchema' AS xsd,

'ns0:cape_site' AS ns1),

OrderedData AS (

SELECT jc.WBSNumber,

jst.EquipmentNo,

SUM(jst.Qty) as Qty,

ROW_NUMBER() OVER(PARTITION BY jc.WBSNumber ORDER BY jst.EquipmentNo) AS rn

FROM Job_Card jc

JOIN Job_Stock_Tbl jst

ON jc.CapeId = jst.CapeId

GROUP BY jc.WBSNumber,

jst.EquipmentNo

)

SELECT CASE WHEN rn=1 THEN WBSNumber END AS "site_code",

EquipmentNo AS "erected_equipment_list/equipment_code",

Qty AS "erected_equipment_list/erected_qnty"

FROM OrderedData

FOR XML PATH(''),ROOT('ns1:site_equipment'),TYPE;

Thanks!

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 13

DECLARE @xml_result XML;

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi,

'http://www.w3.org/2001/XMLSchema' AS xsd,

'ns0:cape_site' AS ns1),

OrderedData AS (

SELECT jc.WBSNumber,

jst.EquipmentNo,

SUM(jst.Qty) as Qty,

ROW_NUMBER() OVER(PARTITION BY jc.WBSNumber ORDER BY jst.EquipmentNo) AS rn

FROM Job_Card jc

JOIN Job_Stock_Tbl jst

ON jc.CapeId = jst.CapeId

GROUP BY jc.WBSNumber,

jst.EquipmentNo

)

SELECT @xml_result = (

SELECT CASE WHEN rn=1 THEN WBSNumber END AS "site_code",

EquipmentNo AS "erected_equipment_list/equipment_code",

Qty AS "erected_equipment_list/erected_qnty"

FROM OrderedData

FOR XML PATH(''),ROOT('ns1:site_equipment'),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

BOR15K SSCertifiable Points: 5894 More actions · Answer 14

BOR15K

SSCertifiable

Points: 5894

July 14, 2011 at 2:07 am

#1352613

Oh boy.....:blush: