How to Read Xml in Sql Server

Query XML information 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 tin 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 tin can be hands read using SQL XML Select statements.

In this SQL Server XML tutorial, I desire 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 information for this SQL tutorial, nosotros accept SQL Server books (book titles) stored in XML format. In T-SQL batch script, offset I ascertain an XML data type variable @SQLXML to store XML data. Then I set the XML data type SQL variable to the XML list we accept which includes SQL Server books and their names.

DECLARE @SQLXML XML
SET @SQLXML = '
<books>
<sql>
<volume id="one">Pro SQL Server 2008 XML</book>
<book id="two">Professional person SQL Server 2005 XML</book>
<book id="3">SQL Server 2012 Programming</book>
<volume id="4">Pro T-SQL 2012 Programmer''s Guide</book>
</sql>
</books>
'

SELECT @SQLXML

Lawmaking

The final SQL Select statement returns XML data.

If you click on the returned XML data of the above SQL Server query, you lot tin can run into the XML data meliorate formatted in XML editor in SQL Server Direction Studio

<books>
<sql>
<book id="1">Pro SQL Server 2008 XML</book>
<book id="2">Professional SQL Server 2005 XML</book>
<volume id="iii">SQL Server 2012 Programming</book>
<book id="iv">Pro T-SQL 2012 Developer'due south Guide</volume>
</sql>
</books>


Lawmaking

SQL XML Query to Catechumen XML to SQL Data

Now, permit's code in SQL and return the list of SQL Server books out of the XML data. Execute the following SQL XML Select argument 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') equally books(title)

Code

Every bit you see in beneath 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 nether the hierarchy /books/sql/ will be returned as the result gear up of this SQL query

You can think of this SQL XML query as follows to empathize its construction:
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) tin 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 cord information blazon similar varchar(100).

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

Wait at the first detail in the SELECT list. The XML node which is represented past title.value is used again. This fourth dimension instead of XML node text information ( "." ) which is the text value between XML node tags, SQL programmer reads XML aspect id using @id annotation. And the id attribute data of the XML node book is converted into smallint SQL Server numeric data type.

SELECT
championship.value('@id','smallint') as [Volume Id],
title.value('.','varchar(100)') equally [Book Championship]
FROM @SQLXML.nodes('/books/sql/book') as books(championship)

Lawmaking

Here is the output outcome of the to a higher place SQL Server XML query later on information technology is executed on SQL Server 2012 using SSMS.

Import XML to SQL Server Table every bit Rows

SQL programmers are at present ready to catechumen XML to SQL information 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 argument.

-- 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 ([Volume Id], [Book Title])
SELECT
title.value('@id','smallint') as [Volume Id],
title.value('.','varchar(100)') as [Book Title]
FROM @SQLXML.nodes('/books/sql/book') every bit books(title)

Code

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

whitehousecauns1945.blogspot.com

Source: https://www.kodyaz.com/t-sql/query-xml-using-sql-xml-in-sql-server.aspx

Share this post