XQuery is to XML what SQL is to database tables. XQuery is designed to query XML data – not just XML files, but anything that can appear as XML, including databases. In SQL Server, there are five methods that you can use to speed up your xquery to a column of type “xml:” query(), value(), exist(), modify(), and nodes(). I’ll cover the first, and most commonly used, three.
For the purpose of this example, I will use the following sample xml in field “XmlField :”
<msg>
<head>
<Id>X9356G356Y</Id>
</head>
<body>
<ReturnedResponse>
<Response>
<ReturnCode>0</ReturnCode>
<Message>SUCCESS</Message>
</Response>
<Customer>
<CustomerId>CID459283</CustomerId>
<State>NV</State>
<Name> <FirstLast>Name1_Name2 </FirstLast>
</Name>
</Customer>
</ReturnedResponse>
</body>
</msg>
–XPATH Examples
–.value
SELECT A.TransDate, A.MainId,
XmlField.value(‘/msg[1]/body[1]/ReturnedResponse[1]/Customer[1]/CustomerId[1]’, ‘VARCHAR(25)’) as xCustomerId
FROM TABLE2 B WITH (NOLOCK)
INNER JOIN TABLE1 A WITH (NOLOCK) ON B.APk = A.MainId
WHERE
A.MainId = ‘HI05491882’
–.exist
SELECT A.TransDate, A.MainId,
XmlField.value(‘/msg[1]/body[1]/ReturnedResponse[1]/Customer[1]/CustomerId[1]’, ‘VARCHAR(25)’) as xCustomerId
FROM TABLE2 B WITH (NOLOCK)
INNER JOIN TABLE1 A WITH (NOLOCK) ON B.APk = A.MainId
WHERE
A.MainId = ‘HI05491882’
AND XmlField.exist(‘/msg/body/ReturnedResponse/Customer/CustomerId’) = 1
–.query
SELECT XmlField.query(‘/msg/body/ReturnedResponse/Customer/CustomerId’)
FROM TABLE2 B WITH (NOLOCK)
INNER JOIN TABLE1 A WITH (NOLOCK) ON B.APk = A.MainId
WHERE
A.MainId = ‘HI05491882’