Category: XML

SQL Server : Use XQuery On XML Data Types

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’

Intro to XML and Serialization

XML (Extensible Markup Language) is a set of rules for encoding documents sent across the internet. In simpler terms, I tend to view XML as more of a format for passing data. Let’s say you were online shopping for a calculator. The website would collect your data. For example,

Name: Master Chief
Address: 50 Forerunner Lane 98356
Item: Calculator
Price 76.98

The data above is easy for you to read if you are human, speak English, and live in America; however, if you are in France then it would need to look different for you to understand. That is one reason we have internet standards, formats, languages, etc. — we want computers “anywhere to be able to read your data and translate it to human verbatum.

If we were to translate your data into read-able XML, then it would first look something like this:

<name>Master Chief</name>
<address>50 Forerunner Lane 98356</address>
<item>Calculator</item>
<price>76.98</price>

Now, for the complicated part — serializing and de-serializing the data. Once your computer is ready to actually send the data to it’s destination, it must be serialized. Serialization is the process of converting the data into a format that can be easily sent across the internet. My definition of serialization is the breaking up of data into small parts so that it can be sent more quickly and efficiently. Once the data reaches it recipient, then it must be deserialized back into the format above.

The data above may seem pretty simple to send so why bother with serialization? Most data is not so simple. What if you want to send a whole book or an entire database? It could take forever! Also, serialization can implement encryption of your private data so that it is more secure and not easily seen by anyone else — handy for credit card information !

And that’s XML and Serialization simplified.