SQL - Query value in XML field

1. Query data in XML field
2. Query data in XML field, condition on Node's Attribute
3. Query data in XML field, condition on Node's TEXT

Example


DECLARE @Messages TABLE (
   ID INTEGER IDENTITY(1, 1),
   [Timestamp] Timestamp,
   xData xml)


SET NOCOUNT ON


INSERT @Messages(xData)
VALUES (
'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<baseElement xmlns="https://xml.abc.net/xmlws/services/v2/"> 
     <nodeElement attribute1="0106" attribute2="0003"> 
<node1 />
<node2>NODE2</node2>
     </nodeElement>
     <nodeElement attribute1="0101" attribute2="0004" /> 
     <nodeElement attribute1="0106" attribute2="0003" /> 
     <nodeElement attribute1="0106" attribute2="0003" /> 
     <nodeElement attribute1="0101" attribute2="0005" /> 
</baseElement> 
</soap:Body>
</soap:Envelope>
')


INSERT @Messages(xData)
VALUES (
'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<baseElement xmlns="https://xml.abc.net/xmlws/services/v2/"> 
     <nodeElement attribute1="0106" attribute2="0003"> 
<node1>I am node 1 in entry 2</node1>
<node2>I am node 2 in entry 2</node2>
     </nodeElement>
     <nodeElement attribute1="0101" attribute2="0004" />      
     <nodeElement attribute1="0106" attribute2="0004" /> 
     <nodeElement attribute1="0106" attribute2="0003" /> 
     <nodeElement attribute1="0101" attribute2="0005" /> 
</baseElement>
</soap:Body>
</soap:Envelope>
') 


INSERT @Messages(xData)
VALUES (
'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<baseElement xmlns="https://xml.abc.net/xmlws/services/v2/">
     <nodeElement attribute1="0101" attribute2="0004" /> 
     <nodeElement attribute1="0106" attribute2="0003" /> 
     <nodeElement attribute1="0106" attribute2="0003" /> 
     <nodeElement attribute1="0106" attribute2="0003" /> 
     <nodeElement attribute1="0101" attribute2="0005" /> 
</baseElement>
</soap:Body>
</soap:Envelope>
')




;WITH XMLNAMESPACES
(
  'http://schemas.xmlsoap.org/soap/envelope/' AS soap,
  'https://xml.abc.net/xmlws/services/v2/' as hc
)


SELECT *
,xData.query('soap:Envelope/soap:Body/hc:baseElement/hc:nodeElement') as nodes
,xData.query('soap:Envelope/soap:Body/hc:baseElement/hc:nodeElement/hc:node1') as node1
FROM @Messages
-- Condition on ATTRIBUTE
WHERE xData.exist('soap:Envelope/soap:Body/hc:baseElement/hc:nodeElement[@attribute1="0106" and @attribute2="0004"]') = 1 
-- Condition on TEXT
or xData.exist('soap:Envelope/soap:Body/hc:baseElement/hc:nodeElement/hc:node2/text()[.="NODE2"]') = 1