Generate XML file from sql

1

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"?> 2020-03-07

Please find the DDL and sample data below:

CREATE TABLE [dbo].[CSV_Convert](
[Store_no] [bigint] NULL,
[Date] varchar 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:


<Store_no>16</Store_no>
7th March 2020

Can somebody please help on this ?

Thanks.

2

A few things to keep in mind:

  1. When you want to store a date, use a DATE or DATETIME data type. Using VARCHAR for dates is not a recommended practice.
  2. If you do use VARCHAR, always specify a length. E.g. VARCHAR(32).

The following code should produce what you are asking for.

DROP TABLE IF EXISTS #CSV_Convert;
CREATE  TABLE #CSV_Convert
(
    [Store_no] [bigint] NULL,
    [Date] Date NULL
);

INSERT INTO #CSV_Convert
VALUES('16','20200307')

INSERT INTO #CSV_Convert
VALUES('17','20200305')

SELECT 
    Store_no,
    Date
FROM
#CSV_Convert FOR XML PATH('Store'), ROOT('Stores');

This relies on default conversion of Date to string. If that is not the format you want for date, you can use CONVERT function, i.e, replace the "Date" just before the "FROM" clause with
CONVERT(VARCHAR(32),Date,23) AS Date

See here for format styles.

1 Like
3

Please show us what you used to generate that xml. Never mind, @JamesK has answered it nicely