Generate XML file from SQL

  • pwalter83

    SSChampion

    Points: 14557

    Hi,

    I need to generate an XML file from the SQL data. The XML file needs to be in the following format:

    <?xml version="1.0" encoding="UTF-8"?>

    <stores xmlns="http://www.demandware.com/xml/impex/store/2007-04-30">

    <store store-id="16">

    <custom-attributes>

    <custom-attribute attribute-id="collectionDate" xml:lang="x-default">2020-03-07</custom-attribute>

    </custom-attributes>

    </store>

    </stores>

    Please find the DDL and sample data below:

    CREATE TABLE [dbo].[CSV_Convert]

    ([Store_no] [bigint] NULL,
    [Date] [varchar](50) NULL) ON [PRIMARY]


    INSERT INTO [dbo].[CSV_Convert]
    VALUES('16','7th March 2020')

    INSERT INTO [dbo].[CSV_Convert]
    VALUES('17','5th March 2020')

    INSERT INTO [dbo].[CSV_Convert]
    VALUES('21','4th March 2020')

    INSERT INTO [dbo].[CSV_Convert]
    VALUES('29','9th March 2020')

    The XML I have been able to generate is not in a correct format as below:

    <stores>

    <store>

    <Store_no>16</Store_no>

    <Date>7th March 2020</Date>

    </store>

    Can somebody please help on this ?

    Thanks.

  • Mark Cowne

    One Orange Chip

    Points: 26748

    This is as near as I can get. You'll have to add '<?xml version="1.0" encoding="UTF-8"?>' manually. Also you've defined the date as a varchar, it should be a date or datetime. It can't really be decoded as is.

     

    WITH XMLNAMESPACES (DEFAULT 'http://www.demandware.com/xml/impex/store/2007-04-30') 
    SELECT Store_no AS '@store-id',
    'collectionDate' AS 'custom-attributes/custom-attribute/@attribute-id',
    'x-default' AS 'custom-attributes/custom-attribute/@xml:lang',
    Date AS 'custom-attributes/custom-attribute'
    FROM dbo.CSV_Convert
    FOR XML PATH('store'),ROOT('store'),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

Viewing 2 posts - 1 through 2 (of 2 total)

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