Generate XML file from SQL

• pwalter83

  SSChampion

  Points: 14575

  More actions

  April 8, 2020 at 10:17 am

  #3741351

  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: 26952

  More actions

  April 8, 2020 at 1:52 pm

  #3741424

  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