SQL Server Convert XML to String

by

In this Python tutorial, you will learn about SQL Server to convert XML to string.

As you already know, you can’t use the XML data as a string if it is in the XML format. I mean, you can’t use it in your app. To use the XML data, you need to convert it into a string, use it like a string, and perform various string operations.

Sometimes, while performing web scraping, I need to store raw XML in the database, so I use the column as the XML type and convert it into a string to save it in a database for future analysis.

This is a beneficial technique that you must know. In this tutorial, I have explained the two functions that you can use to convert the given XML data to a string.

SQL Server Convert XML to String

Before I explain how SQL Server converts XML to string, I want to define XML, which stands for eXtensible Markup Language. XML stores and transports data due to its self-describing nature and flexibility.

The SQL Server has an XML datatype, which you can use to define the column type and store the XML data in that column. People store the XML data using the XML data type in the SQL Server.

But you can’t do that when presenting XML data in applications. You must convert it into a string and then perform any operation on it. You can also use it for various purposes, such as displaying the XML data as information, generating reports, etc.

SQL Server has two functions to convert this XML data: CAST() and CONVERT(). The following section explains how to use the CAST() and CONVERT() functions to convert XML data to a string.

Converting XML to String Using CAST() Function

The CAST() function takes the value and data type to which you want to convert the given value. So here, we will pass the XML data to CAST() and type the conversation data as a string to convert the XML to a string.

First, Let’s create a table named ‘XmlTable’ with columns ‘ID’ and ‘Data’ of type INT and XML respectively.

CREATE TABLE XmlTable (
	ID INT PRIMARY KEY,
	Data XML
);

Now, insert the ten records into the XmlTable using the code below.

INSERT INTO XmlTable (ID, Data) VALUES
(1, '<Customer><Name>Kyrie</Name><Age>30</Age></Customer>'),
(2, '<Customer><Name>Patrick</Name><Age>25</Age></Customer>'),
(3, '<Customer><Name>Richard</Name><Age>22</Age></Customer>'),
(4, '<Customer><Name>Grant</Name><Age>40</Age></Customer>'),
(5, '<Customer><Name>Avery</Name><Age>35</Age></Customer>'),
(6, '<Customer><Name>King</Name><Age>28</Age></Customer>'),
(7, '<Customer><Name>Caden</Name><Age>45</Age></Customer>'),
(8, '<Customer><Name>Adonis</Name><Age>20</Age></Customer>'),
(9, '<Customer><Name>Tristan</Name><Age>32</Age></Customer>'),
(10, '<Customer><Name>Javier</Name><Age>50</Age></Customer>');

View the table.

SELECT * FROM XmlTable;
Creating Table Containing XML Data Before Converting XML to String Using CAST() Function

Look at the table containing the XML data in column ‘Data’; this data represents the customer information such as name and age.

Suppose you must convert the ‘Data’ column XML values to string (nvarchar). You can use the CAST() function, as shown in the code below.

SELECT CAST(Data as NVARCHAR(MAX)) AS Cutomer_String
FROM XmlTable;
Converting XML to String Using CAST() Function

Look at the result set, which contains the string of XML data. Let’s understand the code part, CAST(Data as NVARCHAR(MAX)). Here, the CAST() function takes the first argument, XML values in the column ‘Data’, and the second argument is the data convert type, NVARCHAR(MAX), a string in SQL Server.

Thus, the CAST function converts the given XML data into a string, as shown in the above output.

This is a simple example, but the CAST() function can easily convert more complex XML data into strings.

Converting XML to String Using CONVERT() Function

To convert the XML to string using the CONVERT() function, you will need to pass the first argument to the CONVERT() function as the data type to which you want to convert the given value, and the second is the exact value that you want to transform into a specified data type.

For example, we will take the same ‘XmlTable’ created in the above section. To convert the ‘Data’ column containing the XML data to a string, you can use the CONVERT() function, as shown below.

SELECT CONVERT(NVARCHAR(MAX), Data) AS Cutomer_String
FROM XmlTable;
Converting XML to String Using CONVERT() Function

Look at the output; it seems similar, right? It resembles the result generated by the CAST() function. So, as the CAST() function, the CONVERT() also converts the given XML data into a string.

So, the output, after converting the XML data, is the string you can see in the output.

So, I hope that you understand how the above code converts the XML data in the column ‘Data’ using the CONVERT() function.

I hope you understand how to convert the XML to string using the CAST() and CONVERT() functions from the above two examples.

Conclusion

In this SQL Server tutorial, you learned how SQL Server converts XML to string and about CAST() and CONVERT() functions to convert the XML data into string type.

You also learned about XML data, creating a table with XML-type columns, and inserting 10 records containing XML data.

You may like to read: