SQL SERVER – Introduction to Discovering XML Data Type Methods – A Primer

This blog post is inspired from SQL Interoperability Joes 2 Pros: A Guide to Integrating SQL Server with XML, C#, and PowerShell – SQL Exam Prep Series 70-433 – Volume 5.

[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]


What are XML Data Type Methods

The XML data type was first introduced with SQL Server 2005. This data type continues with SQL Server 2008 where expanded XML features are available, most notably is the power of the XQuery language to analyze and query the values contained in your XML instance.

There are five XML data type methods available in SQL Server 2008:

  1. query() – Used to extract XML fragments from an XML data type.
  2. value() – Used to extract a single value from an XML document.
  3. exist() – Used to determine if a specified node exists. Returns 1 if yes and 0 if no.
  4. modify() – Updates XML data in an XML data type.
  5. node() – Shreds XML data into multiple rows (not covered in this blog post).

XML data type methods require an XPath expression as one of the (or the only) XQuery parameter(s), but not all of the methods return data. Some of these methods simply analyze the data at that level and return a status to you. In order to be proficient with any of the XML data type methods, it is important for you to become familiar with using XPath expressions.

The query() Method

This method basically needs an XPath expression in the XQuery parameter and returns an XML data type. The XPath expression (‘/Music/Song[1]/Singer[2]/BandName’) specifies that we want to navigate to the BandName element of the second Singer of the first Song. The query( ) method returns the XML fragment containing everything between (and including) the beginning and ending tags of that BandName element, which in this example is UB40.

query() method Screenshot 1a

By left-clicking on the hyperlink result, a new XML query window launches within SQL Server Management Studio displaying the full XML result set.

query() method Screenshot 1b

Essentially the query( ) method only returns XML data — if you need a single name or value, then you must add text( ) to the XPath expression in your query( ) parameter.

Review the screenshot below to see how the text( ) function pulls out just the BandName value (stripping off the element tags and only displaying the remaining text).

query() method Screenshot 1c

The value() Method

The next method to explore is value( ). Previously, the query( ) method was used in combination with the text( ) function to pull out just the element text for a single value. Recall that it pulled out the data converted to text (not to XML). When pulling out numerical data utilizing the text() function all data is formatted as text. What if you need a data type other than text?

The value( ) method achieves the same goal as query( ) and text( ) do together, except it allows you to specify the data type you want for your result. It returns just your data (without the metadata – no element tags) and gives you the freedom to specify any data type you would like. In other words, if you are pulling from a element, then you might want the result returned as a Money or a Decimal data type. If you are pulling from the element, you might specify that the returning data should be a varchar data type.

You will find that this method provides an efficient way to retrieve data directly from an XML file and return it to SQL Server. The query syntax is very similar to the previous example. You will use value( ) to return the same data (first Singer of the first Song) by substituting “value” in place of “query” and run the code.

The resulting error message shown in the screenshot below is a reminder to supply a second parameter. The value( ) method requires two parameters.

value() method Screenshot 2a

Observe that XQuery gives us the freedom to specify data types which are compatible with character data (e.g., char(20), varchar(max)).

value() method Screenshot 2b

The exist() Method

In this method you do not want any data returned from the XML stream. You just want a simple check to know whether it is there. The exist( ) method will check for the existence and even the value of the XPath expression you specify.

In this example you will use the exist( ) method to determine if a particular song is in the catalogue. The song TitleID you are looking for is 13161. As you can see from the screenshot, this TitleID does exist as indicated by the return value of 1 (numeric value for ‘Yes’).

exist() method Screenshot 3a

You can now modify your code to look for TitleID 13162. A quick look at the screenshot clearly shows that this song is not in the catalogue, since the return value is a 0 (numeric value for ‘No’).

exist() method Screenshot 3b

The modify() Method

The modify( ) method allows you to change values directly in your XML stream. Like all other XML data type methods, it needs an XPath parameter to know which value to change. However, unlike the other methods, modify( ) works with an UPDATE statement (it will not work with a SELECT statement). Also, modify( ) can only work with one data value at a time, which is a mathematical and programming concept known as a singleton.

Since there is no limit to the number of elements which can be under another element, any given XPath may have many children. For example, the XPath of /week/day shown below has three elements and, therefore, is not a singleton:

modify() method Screenshot 4e

However, if you changed your XPath to (/week/day)[1], then you would only get Monday in your result. Again, since each Song can only have one Title, then specifying the singelton of [1] doesn’t change the appearance of your current result but it helps prepare for the next method to explore, which is modify( ).

It is time for your code to pull out an XML fragment for the Title element. While it will not always be needed, you should add a“[1]” to explicitly specify that you only want a single item retrieved.

modify() method Screenshot 4a

The modify( ) method has the sole purpose to change a value in an XML file, which is a helpful capability. Suppose an XML document is imported into SQL Server and you found a typo or need to update just one value. It is no longer necessary to rerun the step to bring in the XML document in order to make that change – you can simply use the modify( ) method and write the change directly to the XML stream contained in the SQL Server instance.

In this example the title “Manic Monday” needs to be changed to “Walk Like an Egyptian”. Since modify( ) is going to replace the current title, Manic Monday, with the new title, you need to add some code to handle the change.

The modify( ) method belongs in the SET clause of an UPDATE statement. You need to add an UPDATE statement and change “SELECT MusicDetails.query” to “SET MusicDetails.modify” Perfect – your revised code runs correctly, and you can see the confirmation (1 row(s) affected).

modify() method Screenshot 4b

Now return to your original query (using the SELECT statement) and run it to confirm that the title was updated correctly.

modify() method Screenshot 4c

modify() method Screenshot 4d


This blog post is inspired from SQL Interoperability Joes 2 Pros: A Guide to Integrating SQL Server with XML, C#, and PowerShell – SQL Exam Prep Series 70-433 – Volume 5.

[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]

Reference: Pinal Dave (http://blog.sqlauthority.com)

27 thoughts on “SQL SERVER – Introduction to Discovering XML Data Type Methods – A Primer

  1. Hi Pinal,
    Nice article and write up. I have a question I extracted the PackageSource of SSIS package in XML, The XML is the complete layout of package. I am trying to locate a string in that XML, How would i do that. The XML is in the form

    3

    Thank You

    Like

  2. Hi,

    I have a question for you that What is “Dynamic Stored Procedure”? What is this term why not we use SQL Dynamic Query. I am using C#.

    Thanks

    Like

  3. Pingback: SQL SERVER – Quiz and Video – Introduction to Discovering XML Data Type Methods « SQL Server Journey with SQL Authority

  4. Pingback: SQL SERVER – SQL in Sixty Seconds – 5 Videos from Joes 2 Pros Series – SQL Exam Prep Series 70-433 « SQL Server Journey with SQL Authority

  5. 18
    2008

    I have a column in my table named XMLTest with xml datatype and it contain the above lnformation

    I wish to update the built year node i.e. 2008 to 4 like wise 2010 to 2 etc and there are millions of records.

    Actually i wish to replace the given year with difference of current year minus the given year. So the main concern is how to update that in xml.

    Like

  6. Pingback: SQL SERVER – 5 Videos from Joes 2 Pros Series Exam Prep Series 70-433 – SQL in Sixty Seconds « SQL Server Journey with SQL Authority

  7. Hi Pinal,

    Is it possible to replace all the occurrences of a particular value in entire XML with something else ?
    My XML is dynamically generated and not feasible to provide a XPath in the query or modify.
    Wanted to check if there is a way to update the XML in this case.

    Thanks,
    Parvinder

    Like

  8. How to get the entire text of Dynamic query according to the dynamic inputs by Sql query outside the procedure and without executing the procedure

    Like

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

  10. Hi,
    I getting error while i am updating the xml datatype column

    update dept set userdata=’ j&j

    If i remove ” & ” then its updating. what i need to do if i want to execute with ” & “.

    Like

  11. hi,

    the following query executes fine.

    declare @hexbin varbinary(max);
    set @hexbin = 0x5400650073007400 ;
    select cast(” as xml).value(‘xs:hexBinary(sql:variable(“@hexbin”) )’, ‘varchar(max)’);

    I want to apply this query on a column. Can anyone tell me how can I replace variable @hexbin with a column of type timestamp/varbinary.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s