SQL Server parses XML data in detail, sqlxml

SQL Server parses XML data in detail, sqlxml

This document describes how SQL Server parses XML data. We will share this with you for your reference. The details are as follows:

-- 5. read XML -- The following are multiple ways to read EMAILDECLARE @ x XMLSELECT @ x = '<People> <dongsheng> <Info Name = "Email"> dongsheng@xxyy.com </Info> <Info> name = "Phone"> 678945546 </Info> <Info Name = "qq"> 36575 </Info> </dongsheng> </People> '-- Method 1 SELECT @ x. value ('data (/People/dongsheng/Info [@ Name = "Email"]) [1] ', 'varchar (30)') -- method 2 SELECT @ x. value ('(/People/dongsheng/Info [@ Name = "Email"]) [1]', 'varchar (30) ') -- Method 3 select c. value ('. ', 'Varchar (30)') FROM @ x. nodes ('/People/dongsheng/Info [@ Name = "Email"]') T (C) -- Method 4 select c. value ('(Info [@ Name = "Email"]) [1]', 'varchar (30) ') FROM @ x. nodes ('/People/dongsheng') T (C) -- Method 5 select c. value ('(dongsheng/Info [@ Name = "Email"]) [1]', 'varchar (30) ') FROM @ x. nodes ('/others') T (C) -- Method 6 select c. value ('. ', 'varchar (30)') FROM @ x. nodes ('/People/dongsheng/info') T (C) where c. value ('(. [@ Name = "Email"]) [1] ', 'v Archar (30) ') is not null -- Method 7 select c. value ('. ', 'varchar (30)') FROM @ x. nodes ('/People/dongsheng/info') T (C) where c. exist ('(. [@ Name = "Email"]) [1] ') = 1--6. reading values from an XML variableDECLARE @ x XMLSELECT @ x = '<les> <People Name = "tudou" Sex = "female"/> <People Name = "choushuigou" Sex =" female "/> <People Name =" dongsheng "Sex =" male "/> </LES> 'select v. value ('@ Name [1]', 'varchar (20) ') AS Name, v. value ('@ S Ex [1] ', 'varchar (20)') AS SexFROM @ x. nodes ('/LES/others') x (v) -- 7. multi-attribute filter DECLARE @ x XMLSELECT @ x = '<Employees> <Employee id = "1234" dept = "IT" type = "contractor"> <Info NAME = "dongsheng" SEX = "male" QQ = "5454545454"/> </Employee> <Employee id = "5656" dept = "IT" type = "temporary"> <Info NAME = "" SEX = "female" QQ = "5345454554"/> </Employee> <Employee id = "3242" dept = "market" type = "contractor"> <Info NAME = "choushuigou "SEX =" female "QQ =" 54543545" /> </Employee> </Employees> '-- query the information of people whose dept is IT -- Method 1 select c. value ('@ NAME [1]', 'varchar (10) ') as name, C. value ('@ SEX [1]', 'varchar (10) ') as sex, C. value ('@ QQ [1]', 'varchar (20) ') as qq from @ x. nodes ('/Employees/Employee [@ dept = "IT"]/info') T (C) /* name sex qq ---------- -------------------- dongsheng male 5454545454 Tudou female 5345454554 */-- method 2 select c. value ('@ NAME [1]', 'varchar (10) ') as name, C. valu E ('@ SEX [1]', 'varchar (10) ') as sex, C. value ('@ QQ [1]', 'varchar (20) ') as qq from @ x. nodes ('// Employee [@ dept = "IT"]/*') T (C) /* name sex qq ---------- -------------------- dongsheng male 5454545454 Tudou female 5345454554 */-- query the select c. value ('@ NAME [1]', 'varchar (10) ') as name, C. value ('@ SEX [1]', 'varchar (10) ') as sex, C. value ('@ QQ [1]', 'varchar (20) ') as qqfrom @ x. nodes ('// Employee [@ Dept = "IT"] [@ type = "contractor"]/* ') T (C)/* name sex qq ---------- ---------------------- dongsheng male 5454545454 */-- 12. delete the element DECLARE @ x XMLSELECT @ x = '<les> <People> <NAME> Tudou </NAME> <SEX> male </SEX> <QQ> 5345454554 from the XML variable. </QQ> </People> </LES> 'set @ x. modify ('delete (/users les/People/SEX) [1] ') SELECT @ x/* <Les> <People> <NAME> potato </NAME> <QQ> 5345454554 </QQ> </People> </Peoples> */-- 19. reads the value of a specified variable element by DEC. LARE @ x XMLSELECT @ x = '<les> <People> <NAME> dongsheng </NAME> <SEX> male </SEX> <QQ> 423545 </QQ> </ people> <NAME> Tudou </NAME> <SEX> male </SEX> <QQ> 123133 </QQ> </People> <NAME> choushuigou </NAME> <SEX> female </SEX> <QQ> 54543545 </QQ> </People> </LES> 'Clare @ ElementName VARCHAR (20) SELECT @ ElementName = 'name' SELECT c. value ('. ', 'varchar (20)') as namefrom @ x. nodes ('/LES/People/* [local-name () = SQL: variable ("@ ElementName")] ') T (C) /* NAME -------------------- dongsheng potato choushuigou */-- 20 use wildcards to read element values -- read the root element value DECLARE @ x1 XMLSELECT @ x1 = '<People> dongsheng </People>' SELECT @ x1.value ('(/*/text ()) [1] ', 'varchar (20 )') AS People -- asterisk * indicates an element/* People ------------------ dongsheng */-- read the value of the second-level element DECLARE @ x XMLSELECT @ x = '<People> <NAME> dongsheng </NAME> <SEX> male </SEX> <QQ> 423545 </QQ> </People> 'select @ x. value ('(/*/Text () [1]', 'varchar (20 )') as name/* NAME ------------------ dongsheng */-- read the value of the second child element DECLARE @ x XMLSELECT @ x = '<People> <NAME> dongsheng </NAME> <SEX> male </SEX> <QQ> 423545 </QQ> </People> 'select @ x. value ('(/*/text () [2]', 'varchar (20 )') as sex/* SEX ------------------ male */-- read all second-level sub-element values DECLARE @ x XMLSELECT @ x = '<People> <NAME> dongsheng </NAME> <SEX> male </SEX> <QQ> 423545 </QQ> </People> 'select C. value ('. ', 'Varchar (20)') AS valueFROM @ x. nodes ('/*') T (C)/* value ------------------ dongsheng male 423545 */-- 21. use wildcards to read the element name DECLARE @ x XMLSELECT @ x = '<People> dongsheng </People> 'select @ x. value ('local-name (/* [1]) ', 'varchar (20 )') AS ElementName/* ElementName ------------------ People */-- read the NAME and value of the first element under the root, DECLARE @ x XMLSELECT @ x = '<People> <NAME> dongsheng </NAME> <SEX> male </SEX> </People> 'select @ x. value ('local-nam E (/*) [1]) ', 'varchar (20)') AS ElementName, @ x. value ('(/*/text () [1]', 'varchar (20 )') AS ElementValue/* ElementName ElementValue -------------------- NAME dongsheng */-- read the NAME and value of the second element under the root account DECLARE @ x XMLSELECT @ x = '<People> <NAME> dongsheng> <SEX> male </SEX> </People> 'select @ x. value ('local-name (/*) [2]) ', 'varchar (20)') AS ElementName, @ x. value ('(/*/text () [2]', 'varchar (20) ') S ElementValue/* ElementName ElementValue -------------------- SEX male */-- read all element names and values under the root DECLARE @ x XMLSELECT @ x = '<People> <NAME> dongsheng </NAME> <SEX> male </SEX> </People> 'select C. value ('local-name (.) ', 'varchar (20)') AS ElementName, C. value ('. ', 'varchar (20)') AS ElementValueFROM @ x. nodes ('/*') T (C)/* ElementName ElementValue ------------------ ---------------------- NAME dongshengSEX Male */--- 22. Number of query elements -- there is a People subnode under the root node of Les as follows. DECLARE @ x XMLSELECT @ x = '<les> <People> <NAME> dongsheng </NAME> <SEX> male </SEX> </People> <NAME> tudou </NAME> <SEX> male </SEX> </People> <NAME> choushuigou </NAME> <SEX> female </SEX> </People> </cmdles> 'select @ x. value ('count (/distinct les/People) ', 'int ') AS Children/* Children ----------- 3 */-- SELECT @ x. value ('count (/distinct les/People [1]/*) ', 'int') AS Children/* Children --- -------- 2 */-- in some cases, we may not know the name of the root node or child node, which can be replaced by wildcards. SELECT @ x. value ('count (/*) ', 'int') AS ChildrenOfRoot, @ x. value ('count (/* [1]/*) ', 'int') AS ChildrenOfFirstChildElement/* ChildrenOfRoot ChildrenOfFirstChildElement ------------ limit 3 2 */-- 23. number of query attributes DECLARE @ x XMLSELECT @ x = '<Employees dept = "IT"> <Employee NAME = "dongsheng" SEX = "male" QQ = "5454545454"/> <employee NAME = "" SEX = "female" QQ = "5345454554" TEL = "13954697895"/> </Employees> '-- query the number of attributes of a node. SELECT @ x. value ('count (/Employees/@ *) ', 'int') AS AttributeCountOfRoot/* AttributeCountOfRoot ---------------- 1 */-- number of attributes of the first Employee node SELECT @ x. value ('count (/Employees/Employee [1]/@ *) ', 'int ') AS AttributeCountOfFirstElement/* AttributeCountOfFirstElement -------------------------- 3 */-- SELECT @ x. value ('count (/Employees/Employee [2]/@ *) ', 'int ') AS AttributeCountOfSeconfElement/* AttributeCountOfSeconfElement --------------------------- 4 */-- if the node name is unclear, use the * wildcard to replace SELECT @ x. value ('count (/*/@ *) ', 'int') AS AttributeCountOfRoot, @ x. value ('count (/* [1]/@ *) ', 'int') AS AttributeCountOfFirstElement, @ x. value ('count (/* [2]/@ *) ', 'int ') AS AttributeCountOfSeconfElement/* AttributeCountOfRoot AttributeCountOfFirstElement AttributeCountOfSeconfElement ---------------- returns the value of select c for no node. value ('count (. /@ *) ', 'int') AS AttributeCountFROM @ x. nodes ('/*') T (C)/* AttributeCount ------------ 34 */-- 24. returns the attribute value or NAME of a given position, DECLARE @ x XMLSELECT @ x = '<Employees dept = "IT"> <Employee NAME = "dongsheng" SEX = "male" QQ = "5454545454" /> <Employee NAME = "" SEX = "" QQ = "5345454554" TEL = "13954697895"/> </Employees> '-- returns the first Employee Node SELECT @ x. value ('(/Employees/Employee [1]/@ * [position () = 1]) [1]', 'varchar (20 )') AS AttValue/* AttValue ------------------ dongsheng */-- returns the attribute value of the fourth position of the second Employee node SELECT @ x. value ('(/Employees/Employee [2]/@ * [position () = 4]) [1]', 'varchar (20 )') AS AttValue/* AttValue ------------------ 13954697895 */-- returns the third attribute value of the first element SELECT @ x. value ('local-name (/Employees/Employee [1]/@ * [position () = 3]) [1]) ', 'varchar (20 )') AS AttName/* AttName ------------------ QQ */-- returns the fourth attribute value of the second element SELECT @ x. value ('local-name (/Employees/Employee [2]/@ * [position () = 4]) [1]) ', 'varchar (20 )') AS AttName/* AttName ------------------ TEL */-- return the attribute value DECLARE @ Elepos INT, @ Attpos INTSELECT @ Elepos = 2, @ Attpos = 3 SELECT @ x. value ('local-name (/Employees/Employee [SQL: variable ("@ Elepos")]/@ * [position () = SQL: variable ("@ Attpos")]) [1]) ', 'varchar (20)') AS AttName/* AttName ------------------ QQ */-- 25. determine whether the corresponding attribute DECLARE @ x XMLSELECT @ x = '<Employee NAME = "" SEX = "" QQ = "5345454554" TEL = "13954697895"/exists in XML "/> 'if @ x. exist ('/Employee/@ name ') = 1 SELECT 'exists' AS ResultELSE SELECT 'does not exist' AS Result/* Result ------ Exists */-- pass the variable to determine whether DECLARE @ x XMLSELECT @ x = '<Employee NAME = "potato" SEX = "female" QQ = "5345454554" TEL = "13954697895"/> 'Clare @ att VARCHAR (20) SELECT @ att = 'qq' IF @ x. exist ('/Employee/@ * [local-name () = SQL: variable ("@ att")]') = 1 SELECT 'exists' AS ResultELSE SELECT 'does not exist' AS Result/* Result ------ Exists */-- 26. DECLARE @ x XMLSELECT @ x = '<Employee NAME = "" SEX = "" QQ = "5345454554" TEL = "13954697895"/> 'Clare @ cnt INT, @ totCnt INT, @ attName VARCHAR (30), @ attValue VARCHAR (30) SELECT @ cnt = 1, @ totCnt = @ x. value ('count (/Employee/@ *) ', 'int') -- obtain the total number of attributes -- loopWHILE @ cnt <= @ totCnt begin select @ attName = @ x. value ('local-name (/Employee/@ * [position () = SQL: variable ("@ cnt")]) [1]) ', 'varchar (30) '), @ attValue = @ x. value ('(/Employee/@ * [position () = SQL: variable ("@ cnt")]) [1]', 'varchar (30 )') PRINT 'attribute Position: '+ CAST (@ cnt as varchar) PRINT 'attribute Name:' + @ attName PRINT 'Attribute Value: '+ @ attValue PRINT ''-- increment the counter variable SELECT @ cnt = @ cnt + 1END/* Attribute Position: 1 Attribute Name: NAMEAttribute Value: potato Attribute Position: 2 Attribute Name: SEXAttribute Value: Female Attribute Position: 3 Attribute Name: QQAttribute Value: 5345454554 Attribute Position: 4 Attribute Name: TELAttribute Value: 13954697895 */-- 27. returns the child element DECLARE @ x XMLSELECT @ x = '<Employees dept = "IT"> <Employee NAME = "dongsheng" SEX = "male" QQ = "5454545454 "/> <Employee NAME = "" SEX = "" QQ = "5345454554" TEL = "13954697895"/> </Employees> 'select @ x. query ('(/Employees/Employee) [1]') /* <Employee NAME = "dongsheng" SEX = "male" QQ = "5454545454"/> */SELECT @ x. query ('(/Employees/Employee) [position () = 2]') /* <Employee NAME = "" SEX = "" QQ = "5345454554" TEL = "13954697895"/> */-- get the sub-element DECLARE @ I at the specified position through the variable INTSELECT @ I = 2 SELECT @ x. query ('(/Employees/Employee) [SQL: variable ("@ I")]') -- orSELECT @ x. query ('(/Employees/Employee) [position () = SQL: variable ("@ I")]') /* <Employee NAME = "" SEX = "" QQ = "5345454554" TEL = "13954697895"/> */-- 28. loop traversal to obtain all child elements DECLARE @ x XMLSELECT @ x = '<Employees dept = "IT"> <Employee NAME = "dongsheng" SEX = "male" QQ = "5454545454 "/> <Employee NAME = "" SEX = "" QQ = "5345454554" TEL = "13954697895"/> </Employees> 'Clare @ cnt INT, @ totCnt INT, @ child XML -- counter variablesSELECT @ cnt = 1, @ totCnt = @ x. value ('count (/Employees/Employee) ', 'int') -- loopWHILE @ cnt <= @ totCnt begin select @ child = @ x. query ('/Employees/Employee [position () = SQL: variable ("@ cnt")]') PRINT 'processing Child Element: '+ CAST (@ cnt AS VARCHAR) PRINT 'child element: '+ CAST (@ Child as varchar (100) PRINT ''-- incremet the counter variable SELECT @ cnt = @ cnt + 1END/* Processing child Element: 1 Child element: <Employee NAME = "dongsheng" SEX = "male" QQ = "5454545454"/> Processing Child Element: 2 Child element: <Employee NAME = "" SEX = "" QQ = "5345454554" TEL = "13954697895"/>

Five basic operations on XML data in SQL Server

1. xml. exist
If the input is an XQuery expression, 0, 1 or Null is returned. 0 indicates no, 1 indicates yes, and Null indicates that the input is Null.
2. xml. value
The input is an XQuery expression and returns an SQL Server scalar value.
3. xml. query
The input is an XQuery expression and returns an SQL Server XML stream.
4. xml. nodes
The input is an XQuery expression and returns a column set of XML documents.
5. xml. modify

Use an XQuery expression to insert, update, and delete XML nodes.

The following are examples of the above five operations:

declare @XMLVar xml = '<catalog>    <book category="ITPro">       <title>Windows Step By Step</title>       <author>Bill Zack</author>       <price>49.99</price>    </book>    <book category="Developer">       <title>Developing ADO .NET</title>       <author>Andrew Brust</author>       <price>39.93</price>    </book>    <book category="ITPro">       <title>Windows Cluster Server</title>       <author>Stephen Forte</author>       <price>59.99</price>    </book></catalog>'

1. xml. exist

Select @ XMLVar. exist ('/catalog/Book') ----- returns 1 select @ XMLVar. exist ('/catalog/book/@ category') ----- 1 select @ XMLVar is returned. exist ('/catalog/book1') ----- returns 0 set @ XMLVar = nullselect @ XMLVar. exist ('/catalog/Book') ----- return null

2. xml. value

select @XMLVar.value('/catalog[1]/book[1]','varchar(MAX)')select @XMLVar.value('/catalog[1]/book[2]/@category','varchar(MAX)')select @XMLVar.value('/catalog[2]/book[1]','varchar(MAX)')

Result set:
Windows Step By StepBill Zack49.99 Developer NULL
3. xml. query

select @XMLVar.query('/catalog[1]/book')select @XMLVar.query('/catalog[1]/book[1]')select @XMLVar.query('/catalog[1]/book[2]/author')

The result sets are:

<book category="ITPro"> <title>Windows Step By Step</title> <author>Bill Zack</author> <price>49.99</price></book><book category="Developer"> <title>Developing ADO .NET</title> <author>Andrew Brust</author> <price>39.93</price></book><book category="ITPro"> <title>Windows Cluster Server</title> <author>Stephen Forte</author> <price>59.99</price></book><book category="ITPro"> <title>Windows Step By Step</title> <author>Bill Zack</author> <price>49.99</price></book><author>Andrew Brust</author>

4. xml. nodes

select T.c.query('.') as result from @XMLVar.nodes('/catalog/book') as T(c)select T.c.query('title') as result from @XMLVar.nodes('/catalog/book') as T(c)

The result sets are:

<book category="ITPro"><title>Windows Step By Step</title><author>Bill …………<book category="Developer"><title>Developing ADO .NET</title><author>Andrew …………<book category="ITPro"><title>Windows Cluster Server</title><author>Stephen …………<title>Windows Step By Step</title><title>Developing ADO .NET</title><title>Windows Cluster Server</title>

Set ARITHABORT onDECLARE @ x XMLSELECT @ x = '<les> <People> <Email> 1dongsheng@xxyy.com </Email> <Phone> 678945546 </Phone> <QQ> 36575 </QQ> <Addr> 36575 </Addr> </People> </LES> '-- Method 1 select 1001 as peopleId, p. * FROM (select c. value ('local-name (.) ', 'varchar (20)') AS attrName, C. value ('. ', 'varchar (20)') AS attrValueFROM @ x. nodes ('/*') T (C) -- Layer 3) as p/* 1001 Email 1dongsheng@xxyy.com1001 Phone 6789455461001 QQ 365751001 Addr 36575 */

/* Parse the XML Stored PROCEDURE */alter procedure [dbo]. [sp_ExportXml] @ x xml, @ layerstr nvarchar (max) as declare @ SQL nvarchar (max) BEGIN set arithabort on set @ SQL = 'select p. * FROM (select c. value (''local-name (.) '', ''varchar (20)'') AS attrName, C. value (''. '', ''varchar (20)'') AS attrValue FROM @ xmlParas. nodes (''' + @ layerstr + ''') T (C) as P' -- print @ SQL EXECUTE sp_executesql @ SQL, n' @ xmlParas as xml ', @ xmlParas = @ xEND

DECLARE @x XMLSELECT @x ='<Peoples><People>    <Email>1dongsheng@xxyy.com</Email>    <Phone>678945546</Phone>    <QQ>36575</QQ>    <Addr>36575</Addr></People></Peoples>'EXECUTE sp_ExportXml @x,'/*/*/*'

I hope this article will help you design SQL Server database programs.