Query XML data using SQL XML in SQL Server

SQL Server XML enhancements are worth to note especially with SQL Server 2005 and its successors SQL Server 2008 and SQL Server 2012. SQL programmers can import XML to SQL Server and store XML data in SQL Server tables in table columns which have XML data types. XML data in table columns or stored in XML variables can be easily read using SQL XML Select statements.

In this SQL Server XML tutorial, I want to show how Transact-SQL developers can query XML data to read its node text and attribute values. After we convert XML to SQL, T-SQL developers can easily import XML to SQL Server tables using a simple SQL INSERT INTO statement.

In our sample XML data for this SQL tutorial, we have SQL Server books (book titles) stored in XML format. In T-SQL batch script, first I define an XML data type variable @SQLXML to store XML data. Then I set the XML data type SQL variable to the XML list we have which includes SQL Server books and their names.


DECLARE @SQLXML XML

SET @SQLXML = '

<books>

<sql>

<book id="1">Pro SQL Server 2008 XML</book>

<book id="2">Professional SQL Server 2005 XML</book>

<book id="3">SQL Server 2012 Programming</book>

<book id="4">Pro T-SQL 2012 Programmer''s Guide</book>

</sql>

</books>

'



SELECT @SQLXML

Code

The last SQL Select statement returns XML data.

query XML data in SQL Server

If you click on the returned XML data of the above SQL Server query, you can see the XML data better formatted in XML editor in SQL Server Management Studio


<books>

 <sql>

  <book id="1">Pro SQL Server 2008 XML</book>

  <book id="2">Professional SQL Server 2005 XML</book>

  <book id="3">SQL Server 2012 Programming</book>

  <book id="4">Pro T-SQL 2012 Programmer's Guide</book>

 </sql>

</books>

Code

SQL XML Query to Convert XML to SQL Data

Now, let's code in SQL and return the list of SQL Server books out of the XML data. Execute the following SQL XML Select statement to return the values of the XML nodes <book> for the SQL Server books list contained in XML data.


SELECT

 title.value('.','varchar(100)') as [Book Title]

FROM @SQLXML.nodes('/books/sql/book') as books(title)

Code

As you see in below screenshot from Microsoft SQL Server Management Studio, when I execute this SQL XML Select statement on SQL Server 2012 (or on any other SQL Server versions), the text for book nodes under the hierarchy /books/sql/ will be returned as the result set of this SQL query

Transact-SQL XML query in SQL Server 2012

You can think of this SQL XML query as follows to understand its structure:
SQL Server Transact-SQL query enables SQL programmer to read book nodes under the /books/sql/ hierarchy of the @SQLXML XML data into an imaginery table books with column name title. The alias part books(title) can be anything set by the SQL developer. I preferred to use related names for the alias.
The SELECT statement reads book node text value with title.value in the SELECT list. To successfully read book node XML text, SQL programmer should convert text ( "." ) into a valid string data type like varchar(100).

As you will realize we did not read the XML node attribute values like id attributes in our sample SQL XML data. Let's continue this SQL XML tutorial by reading an additional XML data, the id attribute of XML node book.

Look at the first item in the SELECT list. The XML node which is represented by title.value is used once more. This time instead of XML node text data ( "." ) which is the text value between XML node tags, SQL programmer reads XML attribute id using @id notation. And the id attribute data of the XML node book is converted into smallint SQL Server numeric data type.


SELECT

 title.value('@id','smallint') as [Book Id],

 title.value('.','varchar(100)') as [Book Title]

FROM @SQLXML.nodes('/books/sql/book') as books(title)

Code

Here is the output result of the above SQL Server XML query after it is executed on SQL Server 2012 using SSMS.

SQL Server XML query to Select attribute and text data

Import XML to SQL Server Table as Rows

SQL programmers are now ready to convert XML to SQL data rows according to their requirements. After XML data is converted into data rows, it is easy to import XML data into SQL Server table as new rows using SQL INSERT INTO or SELECT INTO commands. Here is a sample SQL XML query to insert new rows by using SQL XML Select statement.


-- Import XML to new SQL Server database table

SELECT

 title.value('@id','smallint') as [Book Id],

 title.value('.','varchar(100)') as [Book Title]

INTO SQLBooks -- creates a new SQL table for XML data

FROM @SQLXML.nodes('/books/sql/book') as books(title)



-- OR



-- Import XML to existing SQL Server table
INSERT INTO SQLBooks ([Book Id], [Book Title])

SELECT

 title.value('@id','smallint') as [Book Id],

 title.value('.','varchar(100)') as [Book Title]

FROM @SQLXML.nodes('/books/sql/book') as books(title)

Code

SQL programmers or database administrators can choose one of the above methods to insert new rows queried from XML data into SQL database tables.