Remove a blank part of an element in SQL xquery for formatted .xml file.

Question

I have a query that I added a UnitID attribute to which is for product Unit of Measures. If there is a value for UnitID I'd like this to show in the formatted .xml file this query produces. If no value then I don't want it on the file. Here is the script to load some test data into a table and run the query producing the file:

-- 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','Value','Amperage Rating','unit.Elec.Amperes','40'),
('1160577','TCF40','39121606_4903','MultiValue','Class',NULL,'Class CF');


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)}" UnitID="{data($x/UnitID)}">{data($x/AttributeValue)}</Value>,
 <MultiValue AttributeID="{(/root/r[AttributeType="MultiValue"]/AttributeID/text())[1]}" UnitID="{(/root/r[AttributeType="MultiValue"]/UnitID/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;

And notice here that UnitID shows as UnitID="" when there is no value. This needs to be removed. Thanks for any help you can provide.

<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" UnitID="">Blade end</Value>
        <Value AttributeID="Mounting" UnitID="">35 mm DIN Rail, Chassis</Value>
        <Value AttributeID="Amperage Rating" UnitID="unit.Elec.Amperes">40</Value>
        <MultiValue AttributeID="Class" UnitID="">
          <Value>Class CF</Value>
        </MultiValue>
      </Values>
    </Product>
  </Products>
</STEP-ProductInformation>

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,
		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','Value','Amperage Rating','unit.Elec.Amperes','40'),
('1160577','TCF40','39121606_4903','MultiValue','Class',NULL,'Class CF');
-- 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>,
 <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>
}
</Values>')
FROM @tbl AS p
GROUP BY ProductID, ParentID, [Name]
FOR XML PATH('Product'), TYPE, ROOT('Products')
)
FOR XML PATH('STEP-ProductInformation'), TYPE;

Answer 2

Try nullif(UnitId, '').

Answer 3

Hi @tom pratt

How about filter the NULL UnitID value in the Where clause?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.