Need to create a .xml file in specific format using SQL Query.

Question

Post reply

Need to create a .xml file in specific format using SQL Query.

osupratt 68828

Old Hand

Points: 339

April 8, 2023 at 10:04 pm

#4171088

I have a table of data that I need to create a .xml file from in a specific format.

CREATE TABLE [dbo].[XML_TABLE](
[ProductID] [nvarchar](25) NULL,
[Name] [nvarchar](25) NULL,
[ParentID] [nvarchar](51) NULL,
[AttributeType] [nvarchar](10) NULL,
[AttributeID] [nvarchar](255) NULL,
[AttributeValue] [nvarchar](4000) NULL
) ON [PRIMARY]
GO

Here are some values to insert:

INSERT INTO WORK.DBO.XML_TABLE([ProductID], [Name], [ParentID], 
[AttributeType], [AttributeID], [AttributeValue])
VALUES('1557505','DVSCTV- 
HT','39122206_5183','Value','IDW_EnvironmentalConditions','Indoor use 
only'),
('1557505','DVSCTV- 
HT','39122206_5183','Value','IDW_Enclosure','Plastic'),
('1557505','DVSCTV- 
HT','39122206_5183','MultiValue','IDW_Color','Hot')

Here is the current query I have but is not correct (close though):

SELECT (
SELECT *
FROM [WORK].[dbo].[XML_TABLE]
FOR XML PATH('Product'), TYPE, ROOT('Products')
).query('<XmlFormat version="1.0">
<Values>
{
for $x in /Products/Product[AttributeType="Value"]
return <Value AttributeID="{data($x/AttributeID)}">{data($x/AttributeValue)}</Value>
}
<MultiValue>
{
for $x in /Products/Product[AttributeType="MultiValue"]
return <MultiValue AttributeID="{data($x/AttributeID)}">{data($x/AttributeValue)}</MultiValue>
}
</MultiValue>
</Values>
</XmlFormat>');

This gives me this output:

<XmlFormat version="1.0">
<Values>
<Value AttributeID="IDW_EnvironmentalConditions">Indoor use only</Value>
<Value AttributeID="IDW_Enclosure">Plastic</Value>
<MultiValue>
<MultiValue AttributeID="IDW_Color">Hot</MultiValue>
</MultiValue>
</Values>
</XmlFormat>

But I need this output:

<Products>
<Product ID="1557505" UserTypeID="CatalogNumber" ParentID="12345678_0123">
<Name>DVSCTV-HT</Name>
<Values>
<Value AttributeID="IDW_EnvironmentalConditions">Indoor use only</Value>
<Value AttributeID="IDW_Enclosure">Plastic</Value>
<MutliValue AttributeID="IDW_Color">
<Value>Hot</Value>
</MultiValue>
</Values>
</Product>
</Products>

How I get there from my current query I don't have a clue. I am trying to get this imported into a 3rd party application and the format is what they gave me to format the file to. Any help would be appreciated. This is the first time I have worked with this. I spent 2 days figuring out the physical file creation with SSIS and C# and finally got that working but the formatting of the file...I've been banging my head against the wall for several days now.

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

That is not easy! This gets you part of the way, but the MultiValue bit is missing:

DROP TABLE IF EXISTS #XML_TABLE;

CREATE TABLE #XML_TABLE
(
    ProductID NVARCHAR(25) NULL
   ,Name NVARCHAR(25) NULL
   ,ParentID NVARCHAR(51) NULL
   ,AttributeType NVARCHAR(10) NULL
   ,AttributeID NVARCHAR(255) NULL
   ,AttributeValue NVARCHAR(4000) NULL
);

INSERT #XML_TABLE
(
    ProductID
   ,Name
   ,ParentID
   ,AttributeType
   ,AttributeID
   ,AttributeValue
)
VALUES
('1557505', 'DVSCTV-HT', '39122206_5183', 'Value', 'IDW_EnvironmentalConditions', 'Indoor use only')
,('1557505', 'DVSCTV-HT', '39122206_5183', 'Value', 'IDW_Enclosure', 'Plastic')
,('1557505', 'DVSCTV-HT', '39122206_5183', 'MultiValue', 'IDW_Color', 'Hot');

SELECT *
FROM #XML_TABLE xt;

SELECT [@Id] = xt.ProductID
      ,[@UserTypeId] = 'CatalogNumber'
      ,[@ParentID] = xt.ParentID
      ,xt.Name
      ,CAST (REPLACE (REPLACE (xt3.n, '<xxx>', ''), '</xxx>', '') AS XML)
FROM #XML_TABLE xt
    CROSS APPLY
(
    SELECT [@AttributeID] = xt2.AttributeID
          ,xxx = xt2.AttributeValue
    FROM #XML_TABLE xt2
    WHERE xt2.ProductID = xt.ProductID
    FOR XML PATH ('Value'), ROOT ('Values')
) xt3(n)
GROUP BY xt.ProductID
        ,xt.ParentID
        ,xt.Name
        ,xt3.n
FOR XML PATH ('Product'), ROOT ('Products');

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin