Need to create a formatted .xml file using SQL Query.

tom pratt 60 Reputation points
2023-04-08T22:00:04.9433333+00:00
  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. 

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,879 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,201 Reputation points
    2023-04-09T02:54:08.5166667+00:00

    Hi @tom pratt,

    Please try the following solution.

    -- DDL and sample data population, start
    DECLARE @tbl 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 INTO @tbl (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'),
    ('1557505','DVSCTV-HT','39122206_5183','MultiValue','IDW_Color','Purple'), 
    ('1557505','DVSCTV-HT','39122206_5183','MultiValue','IDW_Color','Yellow');
    -- DDL and sample data population, end
    
    SELECT 'Context1' AS [@ImportContext]
    	, 'Context1' AS [@ContextID]
    	, 'Main' AS [@WorkspaceID]
    	, 'false' AS [@UseContextLocale]
    , (
    SELECT ProductID AS [@ID]
    	, 'CatalogNumber' AS [@UserTypeID]
    	, ParentID AS [@ParentID]
    	, Name AS [Name]
    	, (SELECT AttributeType, AttributeID, AttributeValue
    		FROM @tbl AS c
    		WHERE p.ProductID = c.ProductID
    			AND p.ParentID = c.ParentID
    			AND p.[Name] = c.[Name]
    		FOR XML PATH('r'), TYPE, ROOT('root')
    ).query('<Values>
    {
    	for $x in /root/r[AttributeType="Value"]
    	return <Value AttributeID="{data($x/AttributeID)}">{data($x/AttributeValue)}</Value>,
    	<MultiValue AttributeID="{(/root/r[AttributeType="MultiValue"]/AttributeID/text())[1]}">
        {
            for $x in /root/r[AttributeType="MultiValue"]/AttributeValue/text()
            return <Value>{$x}</Value>
        }
        </MultiValue>
    }
    </Values>')
    FROM @tbl AS p
    GROUP BY ProductID, ParentID, [Name]
    FOR XML PATH('Product'), TYPE, ROOT('Products')
    )
    FOR XML PATH('STEP-ProductInformation'), TYPE;
    
    

    Output

    
    <STEP-ProductInformation ImportContext="Context1" ContextID="Context1" WorkspaceID="Main" UseContextLocale="false">
      <Products>
        <Product ID="1557505" UserTypeID="CatalogNumber" ParentID="39122206_5183">
          <Name>DVSCTV-HT</Name>
          <Values>
            <Value AttributeID="IDW_EnvironmentalConditions">Indoor use only</Value>
            <Value AttributeID="IDW_Enclosure">Plastic</Value>
            <MultiValue AttributeID="IDW_Color">
              <Value>Hot</Value>
              <Value>Purple</Value>
              <Value>Yellow</Value>
            </MultiValue>
          </Values>
        </Product>
      </Products>
    </STEP-ProductInformation>
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. tom pratt 60 Reputation points
    2023-04-09T17:59:16.6866667+00:00

    Yitzhak thanks for this. It is amazing. There is one piece though that I failed to mention. So if I add 2 more values to this table for this same product and they are AttributeType='MultiValue': ('1557505','DVSCTV- HT','39122206_5183','MultiValue','IDW_Color','Purple'), ('1557505','DVSCTV- HT','39122206_5183','MultiValue','IDW_Color','Yellow'); the code returns this:

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

    but it needs to return (see following): everything else looks exactly like it should.

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