Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Stairway to XML: Level 10 - Converting XML Data

The Series

This article is part of the Stairway Series: Stairway to XML

Although XML is conceptually simple, its use as an equal partner datatype within a relational database, with full searching, validation and manipulation of data, is not intuitive. Now that the industry is more conscious of the use of semi-structured data and data defined by document markup, it is becoming more important than ever for Database Developers and DBAs to become conversant with the technology and appreciative of the cases where XML technologies enhance applications and their development. Robert Sheldon flexes his talent to make the complicated seem simple.

Previous Levels of this Stairway series covered different ways to work with XML in SQL Server, with much of the focus on the XML methods used to access and update components within an XML instance. One of those methods, value(), lets you return a specific element or attribute value as a T-SQL data type such as VARCHAR. For the most part, this represented the only discussion we’ve had about converting XML data. However, there might be times when you want to convert an entire XML instance or fragment to a character data type or convert a string value to XML.

In this Level, we look at how to convert string values to XML and how to convert XML to character types. We’ll be using variables to demonstrate how these conversions work, so there’s no setup required to try out the examples, other than to have access to a SQL Server instance. I wrote the examples on a local instance of SQL Server 2012, but by no means are you limited to this environment. In addition, the methods shown here to convert data can easily be applied to XML columns. But note that you can convert XML data to and from character types only, such as CHAR or VARCHAR. You cannot, for example, convert XML directly to the DATETIME type.

Converting String Values to XML Data

When converting a string value to XML, you can do so implicitly or explicitly, whether you’re using a literal value or accessing the value through an object configured with a character type. One of the most basic examples of an implicit conversion is to assign a literal string value to an XML object, as I do in Listing 1.

DECLARE @xmlPerson XML;
SET @xmlPerson = '<People><Person>John Doe</Person></People>';
SELECT @xmlPerson;

Listing 1: Implicitly converting a string value to XML

First, I declare the @xmlPerson variable with the XML data type. I then assign the <People> element and its contents to the variable. The element in this case is simply a string value that I assign to the variable. SQL Server automatically converts the literal value to XML. When I then use a SELECT statement to retrieve the value from the variable, it’s returned as an XML fragment, as shown in Listing 2.

<People><Person>John Doe</Person></People>

Listing 2: The XML fragment returned by the T-SQL query

As you can see, an implicit conversion is fairly straightforward, and it’s just as easy to convert the value in an XML object. For instance, in the example shown in Listing 3, I convert the value assigned to a variable configured with the NVARCHAR data type.

DECLARE @strPerson NVARCHAR(100);
DECLARE @xmlPerson XML;
SET @strPerson = '<People><Person>John Doe</Person></People>';
SET @xmlPerson = @strPerson;
SELECT @xmlPerson;

Listing 3: Implicitly converting a character type to XML

First, I declare the @strPerson variable with the NVARCHAR type, and then I declare the @xmlPerson variable with the XML type. Next, I assign the <People> element and its contents (defined as a string literal) to the @strPerson variable. Then I simply assign the @strPerson value to @xmlPerson. Once again, SQL Server automatically converts the data from the NVARCHAR type to the XML type. The SELECT statement returns the same results as the SELECT statement in the previous example. (Refer back to Listing 2.)

We could have just as easily assigned a different character type to the @strPerson variable. For instance, the example shown in Listing 4 assigns the VARCHAR(MAX) data type to the variable.

DECLARE @strPerson VARCHAR(MAX);
DECLARE @xmlPerson XML;
SET @strPerson = '<People><Person>John Doe</Person></People>';
SET @xmlPerson = @strPerson;
SELECT @xmlPerson;

Listing 4: Implicitly converting a character type to XML

Once again, SQL Server automatically converts the character value to XML, and the SELECT statement returns the same results as we saw in the previous examples.

If you plan to port your SQL scripts to another database system, you can’t assume that the system will support implicit conversions in the same way as SQL Server. In such circumstances, you should use the CAST function to explicitly convert your string values to XML. The CAST function conforms to ANSI specifications and consequently is supported by most database systems.

In the example shown in Listing 5, I use the CAST function to convert the @strPerson value to XML before assigning the value to the @xmlPerson variable.

DECLARE @strPerson VARCHAR(MAX);
DECLARE @xmlPerson XML;
SET @strPerson = '<People><Person>John Doe</Person></People>';
SET @xmlPerson = CAST(@strPerson AS XML);
SELECT @xmlPerson;

Listing 5: Using the CAST function to explicitly convert a character type

As you can see, the CAST function takes only two arguments, separated by the AS keyword. The first is the source value, in this case, the @strPerson variable, and the second argument is the target data type—XML. Once again, the SELECT statement returns the same XML element as in the previous examples.

We can easily achieve the same results using the CONVERT function, but we need to structure the arguments differently, as shown in Listing 6.

DECLARE @strPerson VARCHAR(MAX);
DECLARE @xmlPerson XML;
SET @strPerson = '<People><Person>John Doe</Person></People>';
SET @xmlPerson = CONVERT(XML, @strPerson);
SELECT @xmlPerson;

Listing 6: Using the CONVERT function to explicitly convert a character type

In this case, we first specify the target data type (XML) and then the source value (@strPerson), separated by a comma. However, the CONVERT function does not port to other systems; it is specific to T-SQL in SQL Server. The only reason you would use the CONVERT function is to take advantage of additional options available to the function not available to CAST.

Let’s look at a couple examples to better understand how this works. In Listing 7, I start by declaring the two variables and assigning a string value to @strPerson, as I did in the previous examples. But notice that this time I’ve add whitespace and line breaks to the string value.

DECLARE @strPerson VARCHAR(MAX);
DECLARE @xmlPerson XML;
SET @strPerson = '
<People>
  <Person>John Doe</Person>
</People>';
SET @xmlPerson = CONVERT(XML, @strPerson);
SELECT @xmlPerson;

Listing 7: Trying to preserve whitespace and line breaks when converting string data to XML

The whitespace and line breaks have no impact on the XML itself. In fact, when SQL Server converts the string to the XML type, it removes the whitespace and line breaks. Consequently, the SELECT statement returns the same results as the previous examples, as shown in Listing 8.

<People><Person>John Doe</Person></People>

Listing 8: The XML fragment returned without the whitespace and line breaks

If we want to preserve the whitespace and line breaks, we need to add a third argument to the CONVERT function. The SQL Server documentation refers to this as the style argument, which is an integer that specifies how to translate the value returned by the expression in the second argument. The styles available are specific to the data type specified in the first argument. For the XML type, we have only a few options available. Two of those are 0 and 1. The 0 option, which is the default, ignores whitespace and line breaks. The 1 option preserves them. Listing 9 shows the CONVERT function when we include 1 as the third argument.

DECLARE @strPerson VARCHAR(MAX);
DECLARE @xmlPerson XML;
SET @strPerson = '
<People>
  <Person>John Doe</Person>
</People>';
SET @xmlPerson = CONVERT(XML, @strPerson, 1);
SELECT @xmlPerson;

Listing 9: Preserving whitespace and line breaks when converting string data to XML

As you can see, I’ve simply added a comma and the 1 argument to the CONVERT function. Everything else in the example is the same as the preceding one. However, the SELECT statement now returns the XML with the whitespace and line breaks preserved, as shown in Listing 10.

<People>
  <Person>John Doe</Person>
</People>

Listing 10: The XML fragment returned with the whitespace and line breaks

Preserving the whitespace is particularly handy when your XML contains more elements and is subsequently more difficult to read. For instance, Listing 11 includes an additional <Person> element in the string value.

DECLARE @strPerson VARCHAR(MAX);
DECLARE @xmlPerson XML;
SET @strPerson = '
<People>
  <Person>John Doe</Person>
  <Person>Jane Doe</Person>
</People>';
SET @xmlPerson = CONVERT(XML, @strPerson, 1);
SELECT @xmlPerson;

Listing 11: Preserving whitespace and line breaks when converting string data to XML

Once again, I’ve used the CONVERT function with the third argument set to 1. As Listing 12 shows, the results have preserved the additional whitespace and line break.

<People>
  <Person>John Doe</Person>
  <Person>Jane Doe</Person>
</People>

Listing 12: The XML fragment returned with an additional element

Keep in mind, however, as handy as the CONVERT function is, in terms of letting you specify how data is converted, the fact that the function cannot be ported to other systems is an important one. If the possibility exists that you will one day need to run your T-SQL scripts against a system other than SQL Server, then you should use the CAST function, and avoid both implicit conversions and the CONVERT function.

Converting XML values to String Data

At times, you might find it handy to convert XML data to string data. For example, you might decide you don’t need to use the XML data type to store your data and want to switch over to one of the character data types. However, SQL Server does not support implicit conversions from the XML type to a character type. To convert your data in this direction, you must use the CAST or CONVERT function.

If you do try to implicitly convert XML data, you will receive an error. For instance, in Listing 13, I define the same two variables you saw in earlier examples. Only this time, I assign the string value (the <People> element) to the @xmlPerson variable and then assign that variable to the @strPerson variable.

DECLARE @xmlPerson XML;
DECLARE @strPerson VARCHAR(MAX);
SET @xmlPerson = '<People><Person>Jane Doe</Person></People>';
SET @strPerson = @xmlPerson;
SELECT @strPerson;

Listing 13: Trying to implicitly convert XML data to a character type

When I try to run these statements, SQL Server returns the error shown in Listing 14. Notice that the error is at Line 4, which is where I try to implicitly convert the XML value to a VARCHAR(MAX) value.

Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type xml to varchar(max) is not allowed. Use the CONVERT function to run this query.

Listing 14: The error message returned when trying to implicitly convert the XML fragment

This, of course, is an easy fix. Simply use the CAST function to implicitly convert the data, as shown in Listing 15.

DECLARE @xmlPerson XML;
DECLARE @strPerson VARCHAR(MAX);
SET @xmlPerson = '<People><Person>Jane Doe</Person></People>';
SET @strPerson = CAST(@xmlPerson AS VARCHAR(MAX));
SELECT @strPerson;

Listing 15: Using the CAST function to explicitly convert XML data

Notice that I specify the CAST function, with the @xmlPerson variable as the first argument and the VARCHAR(MAX) data type as the second argument. As to be expected, the conversion now works without a hitch, and the SELECT statement returns the expected results, as shown in Listing 16.

<People><Person>Jane Doe</Person></People>

Listing 16: The XML fragment returned by the query

I can also use the CONVERT function to achieve the same results. Listing 17 uses the function with the same two arguments used in the previous example for the CAST function.

DECLARE @xmlPerson XML;
DECLARE @strPerson VARCHAR(MAX);
SET @xmlPerson = '<People><Person>Jane Doe</Person></People>';
SET @strPerson = CONVERT(VARCHAR(MAX), @xmlPerson);
SELECT @strPerson;

Listing 17: Using the CONVERT function to explicitly convert XML data

As we saw when converting string data to XML data, there might be times when we want to preserve the whitespace and line breaks. The obvious solution is to simply add the third argument to the CONVERT function. So let’s look at what happens when we do. In Listing 18, my string value now includes whitespace and line breaks, and my CONVERT function includes 1 as the third argument.

DECLARE @xmlPerson XML;
DECLARE @strPerson VARCHAR(MAX);
SET @xmlPerson = '
<People>
  <Person>Jane Doe</Person>
</People>';
SET @strPerson = CONVERT(VARCHAR(MAX), @xmlPerson, 1);
SELECT @strPerson;

Listing 18: Trying to preserve whitespace and line breaks when converting XML data

Unfortunately, this solution will not preserve the whitespace or line breaks, and our SELECT statement again returns the string as a single line, as shown in Listing 19.

<People><Person>Jane Doe</Person></People>

Listing 19: The XML fragment returned without the whitespace and line breaks

There’s a reason for this. Earlier in this Level, in Listing 1, you saw how SQL Server implicitly converts a string literal to XML when you assign the value to an XML object. When converting the data, SQL Server removes the whitespace and line breaks. As a result, you must explicitly convert the data when first assigning it to your object, as shown in Listing 20.

DECLARE @xmlPerson XML;
DECLARE @strPerson VARCHAR(MAX);
SET @xmlPerson = CONVERT(XML, '
<People>
  <Person>Jane Doe</Person>
</People>', 1);
SET @strPerson = CONVERT(VARCHAR(MAX), @xmlPerson, 1);
SELECT @strPerson;

Listing 20: Preserving whitespace and line breaks when preserving XML data

As you can see, I’ve used the CONVERT function when assigning the data to the @xmlPerson variable and then again when assigning that variable value to the @strPerson variable. As Listing 21 shows, the SELECT statement now returns the expected result.

<People>
  <Person>Jane Doe</Person>
</People>

Listing 21: The XML fragment returned with the whitespace and line breaks

Of course, it makes little sense to explicitly convert a string value to XML and then explicitly convert it back to its original value. But this example helps to demonstrate what happens when converting XML data, so if you get results you don’t expect, you might have some understanding of what’s going on. Also keep in mind that, when viewing an XML document, the application you use might automatically display the XML in a readable format, even though the XML itself doesn’t contain any extra whitespace or line breaks. Yet if you were to view the same XML document as a text file, you might see only one line of text.

Conclusion

As this Lesson has demonstrated, converting XML data to a string value is a relatively easy process when using the CAST or CONVERT function. And converting a string value to XML is just as easy, if not easier. You can use either one of the two functions, or you can let SQL Server implicitly convert the value. Even if you were to use an XML method to retrieve only a fragment from an XML document, you can still convert the output. For example, you might use the query() method to return an XML element and then convert that element to a string. The key is in understanding how XML data is converted. Once you have that understanding, you’ll be better able to work with the XML documents in your database.

This article is part of the Stairway to XML Stairway

Sign up to our RSS feed and get notified as soon as we publish a new level in the Stairway! Rss

Total article views: 4816 | Views in the last 30 days: 72
 
Related Articles
FORUM

convertion

convertion

ARTICLE

Personal Development

Steve Jones thinks that a personal development plan is important and talks today a little about how ...

BLOG

Sales People & Commission

I saw this post by Neil Davidson about sales people being different that discusses how sales people ...

FORUM

How to prevent our database to access any person

How to prevent our database to access any person while other person is administrator

ARTICLE

Bad IT People

What happens when you have bad IT people working in your company? Steve Jones says that they always ...

Tags
stairway series    
xml    
 
Contribute