SQL server simple xml operation tutorial

SQL Server 2005 introduces a local data type called XML. You can create a table with one or more XML columns in addition to the relational columns. Variables and parameters are also allowed. To better support XML model features (such as document order and recursive structure), XML values are stored as large binary objects (BLOB) in an internal format ).

You can use the XML string when saving an XML data to the database. SQL Server automatically converts the string to the XML type and stores it in the database.

With SQL Server's support for XML fields, corresponding, T-SQL statements also provide a large number of XML operations to cooperate with the use of XML fields in SQL Server. This document describes how to use SQL statements to operate on XML.

First, we need to clarify a basic principle that XML data cannot be compared with other data types, that is, XML data cannot appear on any side of the equal sign.

It can be roughly divided into query, modification, and cross-domain query.

The query class includes query (), value (), exist (), and nodes ().

The modified class contains modify ().

The cross-origin query class includes SQL: variable () and SQL: column ().


Create an XML custom database table

Create an xml custom table: all previously checked online

Declare @ xmlDoc xml;

Set @ xmlDoc = '<book id = "0001">

<Title> C Program </title>

<Author> David </author>

<Price> 21 </price>

</Book> ', but it is only learning and cannot be used in projects or in practice. Because few operations are directly performed on these in the SQL memory.

In other words, create a table using SQL statements.

The code is as follows:

Copy code

-- 1. Create xml test database table Xml_Table Author: Fly, Email: feifei12300@126.com Use Fly_Test -- Test Database Go Create table Xml_Table (id int identity primary key, XmlData XML ); -- 2. Insert test data Insert into Xml_Table (XmlData) values ('<Book id = "0001" type = "codeph" text = "/codeph"> <Title> SqlServer2005 </title> <Author> Fly </author> <Price> 21 </price> </Book> '); Insert into Xml_Table (XmlData) values ('<Book id = "0002" type = "codeph" text = "/codeph"> <Title> SqlServer2008 </title> <Author> Fly </author> <Price> 22 </price> </Book> '); Insert into Xml_Table (XmlData) values ('<Book id = "0003" type = "codeph" text = "/codeph"> <Title> SqlServer2012 </title> <Author> Fly </author> <Price> 23 </price> </Book> '); -- 3. Query Select * from Xml_Table;

The result is as follows:

 

Xml operations

For xml operations, do not do too much parsing, if there is unclear can contact me; Emil: feifei12300@126.com

It should be noted that an error is reported if an existing attribute is added to each node or when a node is added, so it is best to first exist ('your condition') = 0;

The code is as follows:

Copy code

-- 4. The XML operation has actually started. -- The XML processing function in SQLServer2005 is obviously enhanced by query (), value (), exist (), modify (), and nodes () -- Query the names and authors of all books Select XmlData. query ('/book') as Title, XmlData. query ('/book/author') as author from Xml_Table; -- Obviously this is not the data we want Select XmlData. value ('(/book/title) [1]', 'nvarchar (max) ') as Title, XmlData. value ('(/book/author) [1]', 'nvarchar (max) ') as Author from Xml_Table; -- Query the information of a book numbered 0001 Select XmlData. value ('(/book/title) [1]', 'nvarchar (max) ') as Title, XmlData. value ('(/book/@ id) [1]', 'nvarchar (max) ') as BookID from Xml_Table Where XmlData. value ('(/book/@ id) [1]', 'nvarchar (max) ') = '123 '; -- The price for modifying the number to 0001 is 11. Update Xml_Table Set XmlData. modify ('replace value of (/book [@ id = "0001"]/price/text () [1] with "11 "'); -- Modify the number of all operators as Fly_12300 Update Xml_Table Set XmlData. modify ('replace value of (/book/author/text () [1] with "Fly_12300 "') -- Check whether the price of 0001 is changed to 11, and all authors change to Fly_12300. Select XmlData. value ('(/book/price) [1]', 'nvarchar (max) ') as Title, XmlData. value ('(/book/@ id) [1]', 'nvarchar (max) ') as BookID, XmlData. value ('(/book/author) [1]', 'nvarchar (max) ') as Author from Xml_Table Where XmlData. value ('(/book/@ id) [1]', 'nvarchar (max) ') = '123 '; -- Add attributes Update Xml_Table Set XmlData. modify ('Insert attribute isbn {"12300321"} into (/book) [1] '); -- Check whether the isbn attribute exists. Select XmlData. value ('(/book/@ isbn) [1]', 'nvarchar (max) ') as isbn, XmlData. value ('(/book/@ id) [1]', 'nvarchar (max) ') as BookID from Xml_Table Where XmlData. value ('(/book/@ id) [1]', 'nvarchar (max) ') = '123 '; -- Add the sub-node category as the Computer category to the value 0001. Update Xml_Table Set XmlData. modify ('Insert <category> Computer </category> before (/book [@ id = 0001]/author) [1] '); -- Check whether the category node is added Select XmlData. value ('(/book/category) [1]', 'nvarchar (max) ') as category, XmlData. value ('(/book/@ id) [1]', 'nvarchar (max) ') as BookID, XmlData from Xml_Table Where XmlData. value ('(/book/@ id) [1]', 'nvarchar (max) ') = '123 '; -- Delete a node Update Xml_Table Set XmlData. modify ('Delete/book [@ id = 0001]/category '); -- Check whether the category node is deleted. Select XmlData. value ('(/book/category) [1]', 'nvarchar (max) ') as category, XmlData. value ('(/book/@ id) [1]', 'nvarchar (max) ') as BookID, XmlData from Xml_Table Where XmlData. value ('(/book/@ id) [1]', 'nvarchar (max) ') = '123 '; -- Nodes () query the book encoding Select ids. value ('@ ID', 'varchar (max)'), ids. value ('(title) [1]', 'nvarchar (max) ') title from Xml_Table Cross apply XmlData. nodes ('// book') as X (ids ); -- Exist () Select XmlData. value ('(/book/@ id) [1]', 'nvarchar (max) ') as BookID From Xml_Table Where XmlData. exist ('(/book/@ id)') = 1 -- determines whether the object exists.

As shown in the following figure:

 

Xml xpath

The code is as follows:

Copy code

Create table Books (ID nvarchar (32) not null, Name nvarchar (64 )); Insert into Books values ('20170101', 'sqlserver2005 '); -- Title: MSSQLServer2005 Insert into Books values ('20170101', 'sqlserver2008 '); -- Title: MSSQLServer2008 Insert into Books values ('20170101', 'sqlserver2012 '); -- Title: MSSQLServer2012 -- Xml path Select id, name from [dbo]. [Books] for xml auto; Select id, name from [dbo]. [Books] for xml auto, ELEMENTS, ROOT ('books '); Select id as 'bookid', NAME as 'bookname' FROM [dbo]. [Books] for xml raw; Select id, name from [dbo]. [Books] for xml raw ('book'), ELEMENTS, ROOT ('books '); Select id, name from [dbo]. [Books] for xml path (''); Select id as 'detail/@ ID', NAME as 'detail/name' FROM [dbo]. [Books] for xml path ('book'), ROOT ('books '); Select stuff (SELECT ';' + Name FROM [dbo]. [Books] for xml path (''), 1, 1 ,'');

As shown in the following figure:

 

Cross-Origin operations

The code is as follows:

Copy code

-- Modify the title attribute based on the ID in the Books table and the XmlData ID attribute in the Xml_Table table. -- That is, MSSQLServer2005 is encoded according to The 0001 name in books. -- Modify the title to MSSQLServer2005 encoded as 0001 in the book of the Xml_Table table. Declare @ data xml Declare @ id nvarchar (36) Declare @ name nvarchar (64) Declare custore_name cursor Select Books. ID, Xml_Table.XmlData, Books. Name From Books, Xml_Table Where Books. ID = Xml_Table.XmlData.value ('(/book/@ id) [1]', 'nvarchar (max )'); OPEN custore_name Fetch next from custore_name into @ id, @ data, @ name WHILE (@ FETCH_STATUS = 0) BEGIN Set @ data. modify ('replace value of (/book/title/text () [1] with SQL: variable ("@ name ")')) Update Xml_Table set XmlData = @ data where XmlData. value ('(/book/@ id) [1]', 'nvarchar (max) ') = @ id Fetch next from custore_name @ Id, @ data, @ name END CLOSE custore_name Deallocate custore_name Select * from Xml_Table

As shown in the following figure:

 

VI. Conclusion

Note: To add or modify attributes or nodes, you must first determine whether exist exists. A cursor is used for cross-origin operations. If you are not familiar with cross-origin operations, you can view relevant information on your own.


Read more: SQL SERVER FOR XML PATH row-to-column instance details