Post reply

XML Output in SQL (works perfect in ORACLE)

  • 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!

  • July 12, 2011 at 6:16 am

    #1351514

    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

  • July 12, 2011 at 6:25 am

    #1351520

    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);

  • July 12, 2011 at 6:49 am

    #1351536

    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

  • July 12, 2011 at 6:54 am

    #1351541

    Let's omit XMLNAMESPACES for a moment,

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

    Thanks

  • July 12, 2011 at 6:57 am

    #1351545

    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

  • July 12, 2011 at 8:09 am

    #1351596

    Thanks

  • July 12, 2011 at 9:37 am

    #1351681

    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');

  • July 12, 2011 at 9:50 am

    #1351701

    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

  • July 12, 2011 at 9:56 am

    #1351711

    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!

  • July 12, 2011 at 10:03 am

    #1351721

    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

  • July 12, 2011 at 10:29 am

    #1351754

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

    Again, thanks for the help.

  • July 14, 2011 at 2:03 am

    #1352611

    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!

  • July 14, 2011 at 2:05 am

    #1352612

    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

  • July 14, 2011 at 2:07 am

    #1352613

    Oh boy.....:blush:

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy