How to search the XML node values stored in table column in SQL Server

Question

I was referring to this : How to query values from xml nodes? and tried to find the best and quick way to search the node values, if exists, but somehow it looks completed explanation than that I was originally thinking.

There are around 10K records in the DB table. One of the column stores the XML in the table, the column value is just similar to this ( with lot many other nodes):

<GrobReportXmlFileXmlFile>
   <GrobReport>
       <ReportHeader>
          <OrganizationReportReferenceIdentifier>Hello</OrganizationReportReferenceIdentifier>
          <OrganizationNumber>Hi</OrganizationNumber>
       </ReportHeader>
  </GrobReport>
   <GrobReport>
       <ReportHeader>
          <OrganizationReportReferenceIdentifier>Find</OrganizationReportReferenceIdentifier>
          <OrganizationNumber>Me</OrganizationNumber>
       </ReportHeader>
  </GrobReport>

The script I have tried is:

 select columnname.value('(GrobReportXmlFileXmlFile/GrobReport/ReportHeader/OrganizationReportReferenceIdentifier/)[Hello]',nvarchar(max)) from Table

Note: My columnname is:

columnname(nvarchar(max),null)

However, its showing error, nvarchar is not recognized by build-in function.

I have changed my query like this:

 select T.[columnname].value('(GrobReportXmlFileXmlFile/GrobReport/ReportHeader/OrganizationReportReferenceIdentifier/)[Hello]','nvarchar(max)') from Table as T

However, in this case I'm getting error as:

Can not call method on nvarchar(max)

Any idea, if .value needs to be replaced by some other function as its type is nvarchar(max)? Is there any alternative for "value" function, that I can try?

I would like to find if any particular value (lets say "Hello") exist in any of the column/node or not?If it exists then it should return me the number of records(rows) in which the searched value present?

Thanks

Answer 1

You can try the following three methods:

1. CTE and rectangular/relational data set with WHERE clause.
2. XPath predicate.
3. .exist() method.

Method #1. A combination of .nodes() and .value() methods convert XML into a rectangular data sets inside the CTE. After that a WHERE clause finds what is needed based on the search criteria.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata NVARCHAR(MAX));
INSERT INTO @tbl (xmldata)
VALUES
(N'<GrobReportXmlFileXmlFile>
    <GrobReport>
        <ReportHeader>
            <OrganizationReportReferenceIdentifier>Hello</OrganizationReportReferenceIdentifier>
            <OrganizationNumber>Hi</OrganizationNumber>
        </ReportHeader>
    </GrobReport>
    <GrobReport>
        <ReportHeader>
            <OrganizationReportReferenceIdentifier>Find</OrganizationReportReferenceIdentifier>
            <OrganizationNumber>Me</OrganizationNumber>
        </ReportHeader>
    </GrobReport>
</GrobReportXmlFileXmlFile>');
-- DDL and sample data population, end

DECLARE @searchParam VARCHAR(20) = 'Hello';

-- Method #1
;WITH cte AS
(
    SELECT ID, TRY_CAST(xmldata AS XML) AS xmldata
    FROM @tbl
), rs AS
(
SELECT ID 
    , c.value('(OrganizationReportReferenceIdentifier/text())[1]','VARCHAR(20)') AS OrganizationReportReferenceIdentifier
    , c.value('(OrganizationNumber/text())[1]','VARCHAR(20)') AS OrganizationNumber
FROM cte AS tbl
    CROSS APPLY tbl.xmldata.nodes('/GrobReportXmlFileXmlFile/GrobReport/ReportHeader') AS t(c)
)
SELECT * FROM  rs
-- apply any filter(s) here
WHERE OrganizationReportReferenceIdentifier = @searchParam;

Output

+----+---------------------------------------+--------------------+
| ID | OrganizationReportReferenceIdentifier | OrganizationNumber |
+----+---------------------------------------+--------------------+
|  1 | Hello                                 | Hi                 |
|  1 | Find                                  | Me                 |
+----+---------------------------------------+--------------------+

Method #2, based on the XPath predicate

-- Method #2
;WITH cte AS
(
    SELECT ID, TRY_CAST(xmldata AS XML) AS xmldata
    FROM @tbl
)
SELECT ID 
    , c.value('(OrganizationReportReferenceIdentifier/text())[1]','VARCHAR(20)') AS OrganizationReportReferenceIdentifier
    , c.value('(OrganizationNumber/text())[1]','VARCHAR(20)') AS OrganizationNumber
FROM cte AS tbl
    CROSS APPLY tbl.xmldata.nodes('/GrobReportXmlFileXmlFile/GrobReport/ReportHeader[(OrganizationReportReferenceIdentifier/text())[1] eq sql:variable("@searchParam")]') AS t(c);

Output

+----+---------------------------------------+--------------------+
| ID | OrganizationReportReferenceIdentifier | OrganizationNumber |
+----+---------------------------------------+--------------------+
|  1 | Hello                                 | Hi                 |
+----+---------------------------------------+--------------------+

Method #3, based on the .exist() method.

-- Method #3
;WITH cte AS
(
    SELECT ID, TRY_CAST(xmldata AS XML) AS xmldata
    FROM @tbl
)
SELECT ID 
    , c.value('(OrganizationReportReferenceIdentifier/text())[1]','VARCHAR(20)') AS OrganizationReportReferenceIdentifier
    , c.value('(OrganizationNumber/text())[1]','VARCHAR(20)') AS OrganizationNumber
FROM cte AS tbl
    CROSS APPLY tbl.xmldata.nodes('/GrobReportXmlFileXmlFile/GrobReport/ReportHeader') AS t(c)
WHERE c.exist('OrganizationReportReferenceIdentifier[. eq sql:variable("@searchParam")]') = 1;