Multiple Multi-Value attributes in xquery showing up as a single attribute in .xml file

Question

I have a query in T-SQL that I've built through extensive help on this forum. It is producing a formatted .xml file and all results are as expected except for when there are multiple 'multi-value' attributes. When this happens only a single Multi-Value attribute element is produced with all the values under this single AttributeID. Below is the query that creates a table and inserts test data. I then show the current file output and under that what I need as an output. Thanks.

-- 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,
		UnitID nvarchar(255) NULL,
    	AttributeValue nvarchar(4000) NULL
);
INSERT INTO @tbl (ProductID, [Name], ParentID, AttributeType, AttributeID, UnitID, AttributeValue)
VALUES 
('1160577','TCF40','39121606_4903','Value','Connection',NULL,'Blade end'),
('1160577','TCF40','39121606_4903','Value','Mounting',NULL,'35 mm DIN Rail, Chassis'),
('1160577','TCF40','39121606_4903','MultiValue','Size','unit.Dime.Inches','MOL: 5, MOD: 2.63'),
('1160577','TCF40','39121606_4903','MultiValue','Color',NULL,'Frost'),
('886092','RC2163BFE','39121552_4775','Value','Connection',NULL,'Blade end'),
('886092','RC2163BFE','39121552_4775','Value','Mounting',NULL,'35 mm DIN Rail, Chassis'),
('886092','RC2163BFE','39121552_4775','Value','Lumens','unit.Elec.Lumens','2.9k');

-- DDL and sample data population, end

--SELECT * FROM @tbl
--FOR XML PATH('r'), TYPE, ROOT('root');

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, UnitID, 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)}">
	 {if ($x/UnitID) then attribute UnitID {$x/UnitID} else ()}
	 {data($x/AttributeValue)}
 </Value>,
 if (/root/r[AttributeType="MultiValue"]) then
	 <MultiValue AttributeID="{(/root/r[AttributeType="MultiValue"]/AttributeID/text())[1]}">
			{ if (/root/r[AttributeType="MultiValue"]/UnitID) 
			then attribute UnitID {/root/r[AttributeType="MultiValue"]/UnitID}
			else () }
		{
			for $x in /root/r[AttributeType="MultiValue"]/AttributeValue/text()
			return <Value>{$x}</Value>
		}
		</MultiValue>
	else ()
}
</Values>')
FROM @tbl AS p
GROUP BY ProductID, ParentID, [Name]
FOR XML PATH('Product'), TYPE, ROOT('Products')
)
FOR XML PATH('STEP-ProductInformation'), TYPE;

Here is the current output with the 2 Multi-Value attributes showing as 1.

<STEP-ProductInformation ImportContext="Context1" ContextID="Context1" WorkspaceID="Main" UseContextLocale="false">
  <Products>
    <Product ID="1160577" UserTypeID="CatalogNumber" ParentID="39121606_4903">
      <Name>TCF40</Name>
      <Values>
        <Value AttributeID="Connection">Blade end</Value>
        <Value AttributeID="Mounting">35 mm DIN Rail, Chassis</Value>
        <MultiValue AttributeID="Size" UnitID="unit.Dime.Inches">
          <Value>MOL: 5, MOD: 2.63</Value>
          <Value>Frost</Value>
        </MultiValue>
      </Values>
    </Product>
    <Product ID="886092" UserTypeID="CatalogNumber" ParentID="39121552_4775">
      <Name>RC2163BFE</Name>
      <Values>
        <Value AttributeID="Connection">Blade end</Value>
        <Value AttributeID="Mounting">35 mm DIN Rail, Chassis</Value>
        <Value AttributeID="Lumens" UnitID="unit.Elec.Lumens">2.9k</Value>
      </Values>
    </Product>
  </Products>
</STEP-ProductInformation>

Below is the wanted or expected output with both Multi-Value attributes now showing and also how the Unit ID is part of the multi-value value line? I have taken what I was helped with here and I've made things worse trying to break this down and figure it out.

<STEP-ProductInformation ImportContext="Context1" ContextID="Context1" WorkspaceID="Main" UseContextLocale="false">
  <Products>
    <Product ID="1160577" UserTypeID="CatalogNumber" ParentID="39121606_4903">
      <Name>TCF40</Name>
      <Values>
        <Value AttributeID="Connection">Blade end</Value>
        <Value AttributeID="Mounting">35 mm DIN Rail, Chassis</Value>
		<MultiValue AttributeID="Size">
		  <Value UnitID="unit.Dime.Inches">MOL: 5</Value>
		  <Value UnitID="unit.Dime.Inches">MOD: 2.63</Value>
		</MultiValue>
		<MultiValue AttributeID="Color">
		  <Value>Frost</Value>
		 </MultiValue>
      </Values>
    </Product>
    <Product ID="886092" UserTypeID="CatalogNumber" ParentID="39121552_4775">
      <Name>RC2163BFE</Name>
      <Values>
        <Value AttributeID="Connection">Blade end</Value>
        <Value AttributeID="Mounting">35 mm DIN Rail, Chassis</Value>
        <Value AttributeID="Lumens" UnitID="unit.Elec.Lumens">2.9k</Value>
      </Values>
    </Product>
  </Products>
</STEP-ProductInformation>

Answer 1

Hi @tom pratt,

Please try the following solution.

I used a CTE for the answer. But in the real world, you can create a view for that.

-- 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,
		UnitID nvarchar(255) NULL,
    	AttributeValue nvarchar(4000) NULL
);
INSERT INTO @tbl (ProductID, [Name], ParentID, AttributeType, AttributeID, UnitID, AttributeValue)
VALUES 
('1160577','TCF40','39121606_4903','Value','Connection',NULL,'Blade end'),
('1160577','TCF40','39121606_4903','Value','Mounting',NULL,'35 mm DIN Rail, Chassis'),
('1160577','TCF40','39121606_4903','MultiValue','Size','unit.Dime.Inches','MOL: 5, MOD: 2.63'),
('1160577','TCF40','39121606_4903','MultiValue','Color',NULL,'Frost'),
('886092','RC2163BFE','39121552_4775','Value','Connection',NULL,'Blade end'),
('886092','RC2163BFE','39121552_4775','Value','Mounting',NULL,'35 mm DIN Rail, Chassis'),
('886092','RC2163BFE','39121552_4775','Value','Lumens','unit.Elec.Lumens','2.9k');
-- DDL and sample data population, end

SELECT * FROM @tbl
--FOR XML PATH('r'), TYPE, ROOT('root');

;WITH rs AS
(
	SELECT ProductID, Name, ParentID, AttributeType, AttributeID, UnitID, AttributeValue = TRIM([value])
	FROM @tbl AS c
		CROSS APPLY STRING_SPLIT(AttributeValue, ',')
	WHERE AttributeType='MultiValue'
	UNION ALL
	SELECT ProductID, Name, ParentID, AttributeType, AttributeID, UnitID, AttributeValue
	FROM @tbl AS c
	WHERE AttributeType='Value'
)
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, UnitID, AttributeValue
FROM rs 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)}">
		{if ($x/UnitID) then attribute UnitID {data($x/UnitID)} else ()}
		{data($x/AttributeValue)}
	</Value>,
	if (/root/r[AttributeType="MultiValue"]) then
		for $x in distinct-values(/root/r[AttributeType="MultiValue"]/AttributeID)
			return <MultiValue AttributeID="{$x}">
			{
				for $y in /root/r[AttributeID/text()=$x]
				return <Value>
				{ 
				  if ($y[AttributeID/text()=$x]/UnitID) then 
					attribute UnitID {$y[AttributeID/text()=$x]/UnitID/text()}
				  else () 
				}
				{data($y/AttributeValue)}
				</Value>
			}
			</MultiValue>
	else ()
}
</Values>')
FROM rs AS p
GROUP BY ProductID, ParentID, [Name]
FOR XML PATH('Product'), TYPE, ROOT('Products')
)
FOR XML PATH('STEP-ProductInformation'), TYPE;