T-SQL XML Output file

Question

I believe there have been a number of posts around this topic but nothing has sprung out so far to get where I need to be...

Basically I am trying to recreate the following XML output file from SQL.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<inventory company_acro_name="TEST" company_mail_to="" hold_on_error="N">
    <product recordtype="INVENTORY" quantity="48" supplierassortmentid="4916464_811-6.5" date="2018-04-25 13:29:32" />
    <product recordtype="INVENTORY" quantity="89" supplierassortmentid="4916464_811-7" date="2018-04-25 13:29:32" />
    <product recordtype="INVENTORY" quantity="136" supplierassortmentid="4916464_811-7.5" date="2018-04-25 13:29:32" />
    <product recordtype="INVENTORY" quantity="57" supplierassortmentid="4916464_811-8" date="2018-04-25 13:29:32" />
    <product recordtype="INVENTORY" quantity="54" supplierassortmentid="4916464_811-8.5" date="2018-04-25 13:29:32" />
    <product recordtype="INVENTORY" quantity="46" supplierassortmentid="4916464_811-9" date="2018-04-25 13:29:32" />
    <product recordtype="INVENTORY" quantity="50" supplierassortmentid="4916464_811-9.5" date="2018-04-25 13:29:32" />
</inventory>

I have thus far managed to generate the data part using the following script

SELECT
    1 as [Tag], 0 as [Parent],
    'INVENTORY' as [Product!1!recordtype],
    CAST([Available_Quantity] as int) as [Product!1!quantity],
    [Product]  + '_' +  [Matrix_Code_1] + '-' + [Matrix_Code_2] as [Product!1!supplierassortmentid],
    CONVERT(smalldatetime,CURRENT_TIMESTAMP) as [Product!1!date]
FROM 
    [dbo].[TStock]
WHERE 
    Company = 4
FOR XML EXPLICIT

However I am struggling to add the first two lines of the required file

Can this be done?

Thanks

Answer 1

FOR XML EXPLICIT is rather outdated. There are a few situations were it is still useful. But your issue is better solved like this:

This is a reduced mockup of your table with some sample data. Please add such an MCVE the next time yourself.

CREATE TABLE TStock  (
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Available_Quantity INT,
YourCode VARCHAR(100)
);

INSERT INTO TStock(Available_Quantity,YourCode) VALUES
 (10,'Code Row 1')
,(20,'Code Row 2')

GO

--The query will use SELECT FOR XML PATH twice

SELECT 'TEST' AS [@company_acro_name]
      ,'' AS [@company_mail_to]
      ,'N' AS [@hold_on_error]
      ,(
        SELECT 'INVENTORY' AS [@recordtype]
              ,Available_Quantity AS [@quantity]
              ,YourCode AS [@supplierassortmentid]
        FROM TStock
        FOR XML PATH('product')
       ,TYPE) AS [*]
FOR XML PATH('inventory')

--Clean up (careful with real data!)

/*
GO
DROP TABLE TStock;
*/

The result

<inventory company_acro_name="TEST" company_mail_to="" hold_on_error="N">
  <product recordtype="INVENTORY" quantity="10" supplierassortmentid="Code Row 1" />
  <product recordtype="INVENTORY" quantity="20" supplierassortmentid="Code Row 2" />
</inventory>

About the xml declaration <?xml blah?> read these links:

https://stackoverflow.com/a/49768800/5089204 (and the linked answer there)

https://stackoverflow.com/a/37477855/5089204