CODEWITHSUNDEEP
sqlsql-serverxmlxquery
spgk
spgk

Reputation: 5

Query XML column using another column

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

ID ProductDetails
2 <XML>
3 <XML>

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?

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

ID ProductDetails ProductTitle
5 <XML> Product5
3 <XML> Product3

Any help would be appreciated.

Upvotes: 0

Views: 361

Answers (2)

T N
T N

Reputation: 10204

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

The subselect in the latter could also be used to build a temp table for more traditional query access.

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.

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22321

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

ID ProductTitle
3 Product4

Upvotes: 1

Related Questions