I have a SQL table ("Products") with 2 columns as below.

The XML Column holds the following data.

<Products>
  <product key="0" description="">Product1</product>
  <product key="1" description="">Product2</product>
  <product key="2" description="">Product3</product>
  <product key="3" description="">Product4</product>
  <product key="4" description="">Product5</product>
  <product key="5" description="">Product6</product>
  <product key="6" description="">Product7</product>
  <product key="7" description="">Product8</product>
</Products>

How can I get the relevant node from the ProductDetails for ProductTitle?

ex: if the ID column has 3, I need to query the ProductDetails column and create a new column with just the ProductTitle to be as Product3.

Any help would be appreciated.

CodePudding user response：

Please try the following.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT PRIMARY KEY, ProductDetails XML);
INSERT @tbl (ID, ProductDetails) VALUES
(3, N'<Products>
  <product key="0" description="">Product1</product>
  <product key="1" description="">Product2</product>
  <product key="2" description="">Product3</product>
  <product key="3" description="">Product4</product>
  <product key="4" description="">Product5</product>
  <product key="5" description="">Product6</product>
  <product key="6" description="">Product7</product>
  <product key="7" description="">Product8</product>
</Products>');
-- DDL and sample data population, end

SELECT ID 
    , ProductDetails.value('(/Products/product[@key=sql:column("ID")]/text())[1]','VARCHAR(20)') AS ProductTitle
FROM @tbl;

Output

CodePudding user response：

You need to use the XML .nodes() function to access and filter the "product" XML elements, and then the .value() function to extract the desired text value from that node. Both take xpath parameters to specify the data to be extracted.

Try

SELECT P.*, X.N.value('text()[1]', 'nvarchar(max)')
FROM @Products P
CROSS APPLY @ProductXml.nodes('/Products/product[@key=sql:column("P.ID")]') X(N)

or equivilently:

SELECT P.*, PN.ProductName
FROM @Products P
CROSS APPLY (
    SELECT ProductName = X.N.value('text()[1]', 'nvarchar(max)')
    FROM @ProductXml.nodes('/Products/product[@key=sql:column("P.ID")]') X(N)
) PN

or even

SELECT P.*, PN.ProductName
FROM @Products P
JOIN (
    SELECT
        ProductKey = X.N.value('@key', 'nvarchar(max)'),
        ProductName = X.N.value('text()[1]', 'nvarchar(max)')
    FROM @ProductXml.nodes('/Products/product') X(N)
) PN ON PN.ProductKey = P.ID

In the xpath strings,

• /Products/product selects all product nodes
• [@key = ...] selects the key attribute to be used as a filter (equality)
• sql:column("P.ID") allows a reference back to a column in the containing SQL
• text() is a special selector that chooses the text within the XML element
• [1] filters that down to at most a single value (a singleton)
• nvarchar(max) defines the result datatype for the .value() function.

See this db<>fiddle.