How to search the XML node values stored in table column in SQL Server
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
You can try the following three methods:
- CTE and rectangular/relational data set with
WHEREclause. - XPath predicate.
.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; 
