CODEWITHSUNDEEP
sql-server
diep
diep

Reputation: 101

SQL server: WHERE in xml field

Example: I have table TableA with 3 records:

record 1:

id = 1, value = '<Employee id='1' name='Employee1'></Employee><Employee id='2' name='Employee2'></Employee>'

record 2:

id = 2, value = '<Employee id='1' name='Employee1'></Employee><Employee id='2' name='Employee2'></Employee><Employee id='3' name='Employee3'></Employee>'

record 3:

id = 3, value = '<Employee id='1' name='Employee1'></Employee><Employee id='2' name='Employee2'></Employee><Employee id='3' name='Employee3'></Employee><Employee id='4' name='Employee4'></Employee>'

the query:

SELECT * FROM TableA
WHERE...

How can I put the where clause to get only record 1?

Many thanks,

Upvotes: 0

Views: 95

Answers (2)

diep
diep

Reputation: 101

I tried with this query and It returns record 1 as I expect:

SELECT * FROM TableA WHERE value.exist('(Employee[@id = 1])') = 1 and value.exist('(Employee[@id = 2])') = 1 AND value.value('count(Employee[@id])', 'int') = 2

Do you have any comments for this query? Should I use it? :)

Upvotes: 0

StuartLC
StuartLC

Reputation: 107367

The problem with the data is that it doesn't contain well formed xml - you will need to wrap it before you can use the xml tools in Sql like xquery.

SELECT *
FROM
(
  SELECT
    Nodes.node.value('(./@id)[1]', 'int') AS EmployeeId,
    Nodes.node.value('(./@name)[1]', 'varchar(50)') AS EmployeeName
  FROM
    (
       SELECT CAST('<xml>' + value + '</xml>' AS Xml) As WrappedXml
       FROM TableA
    ) AS x
    cross apply x.WrappedXml.nodes('//Employee') as Nodes(node)
) as y
WHERE 
   y.EmployeeId = 1;
  • Inner select -wraps the xml
  • Middle select - standard xquery
  • Outer select - where filter

You haven't clarified what you mean w.r.t. get only record 1, but if you mean just the first element of each row (which coincidentally also has id = 1), you can use ROW_NUMBER() to assign a sequence:

SELECT *
FROM
(
  SELECT
    Nodes.node.value('(./@id)[1]', 'int') AS EmployeeId,
    Nodes.node.value('(./@name)[1]', 'varchar(50)') AS EmployeeName,
    ROW_NUMBER() OVER (PARTITION BY x.Id ORDER BY ( SELECT 1 )) SequenceId
  FROM
    (
       SELECT Id, CAST('<xml>' + value + '</xml>' AS Xml) As WrappedXml
       FROM TableA
    ) AS x
    cross apply x.WrappedXml.nodes('//Employee') as Nodes(node)
) as y
WHERE SequenceId = 1;

Both Fiddles here

Upvotes: 2

Related Questions