CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlxquery
triplestones
triplestones

Reputation: 393

Querying XML Child Nodes with WHERE clause via SQL

Is it possible to write a WHERE clause to select values from XML based on the value of a child node?

Example XML

<Message>
   <PrimaryKeyValue>12345</PrimaryKeyValue>
   <Events>
      <UpdatedDefaultAddress>True</UpdatedDefaultAddress>
      <UpdatedCustomerName>False</UpdatedCustomerName>
   </Events>
</Message>
<Message>
   <PrimaryKeyValue>67890</PrimaryKeyValue>
   <Events>
      <UpdatedDefaultAddress>False</UpdatedDefaultAddress>
      <UpdatedCustomerName>True</UpdatedCustomerName>
   </Events>
</Message>

My Question

How would I construct a query to only select PrimaryKeyValues where the value of <UpdatedDefaultAddress> is 'True'?

My current code

SELECT
t.c.value('(PrimaryKeyValue)[1]', 'varchar(100)') AS PrimaryKeyValue
FROM @message_body.nodes('Message') as T(C)
WHERE t.c.exist('./Events/UpdateDefaultAddress') = 'True'

The problem with this is that I get both PrimaryKeyValues back in the result - I think the code is checking whether <UpdatedDefaultAddress> exists, rather than the value contained within it?

Upvotes: 2

Views: 1525

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

Use .value() instead of .exists()

DECLARE @message_body XML =N'<Message>
   <PrimaryKeyValue>12345</PrimaryKeyValue>
   <Events>
      <UpdatedDefaultAddress>True</UpdatedDefaultAddress>
      <UpdatedCustomerName>False</UpdatedCustomerName>
   </Events>
</Message>
<Message>
   <PrimaryKeyValue>67890</PrimaryKeyValue>
   <Events>
      <UpdatedDefaultAddress>False</UpdatedDefaultAddress>
      <UpdatedCustomerName>True</UpdatedCustomerName>
   </Events>
</Message>';

SELECT t.c.value('(PrimaryKeyValue)[1]', 'varchar(100)') AS PrimaryKeyValue
FROM @message_body.nodes('Message') as T(C)
WHERE  t.c.value('(./Events/UpdatedDefaultAddress)[1]', 'varchar(10)')='True';

Rextester Demo

Upvotes: 3

Related Questions