Generate XML file from SQL

  • pwalter83


    Points: 14557


    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="">

    <store store-id="16">


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




    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:




    <Date>7th March 2020</Date>


    Can somebody please help on this ?


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


    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

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

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