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

Question

  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. 

Answer 1

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>

Answer 2

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>