SQL Server how to parse XML data _mssql

The example in this article describes how SQL Server resolves XML data. Share to everyone for your reference, specific as follows:

--5. read XML-read email from XML for a variety of methods DECLARE @x XML SELECT @x = ' <People> <dongsheng> <info name= ' email >dongsheng@xxyy.com</Info> <info name= "Phone" >678945546</Info> <info name= "QQ" >36575&lt ;/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.V Alue ('. ', ' varchar ') from @x.nodes ('/people/dongsheng/info[@Name = ' Email '] ') T (C)--Method 4 SELECT C.value (' (info[@ Name= "Email"]) [1] ', ' varchar ') from @x.nodes ('/people/dongsheng ') T (C)--Method 5 SELECT C.value (' (dongsheng/info[@ Name= "Email"]) [1] ', ' varchar ') from @x.nodes ('/people ') T (C)--Method 6 SELECT C.value ('. ', ' varchar () ') from @x.nodes ( '/people/dongsheng/info ') T (C) WHERE c.value (' (. [ @Name = "Email"]) [1] ', ' varchar ') is not NULL--method 7 SELECT C.value ('. ', ' varchar ') from @x.nodes ('/people/dOngsheng/info ') T (C) WHERE c.exist (' (. [ @Name = "Email"]) [1] = 1--6.reading values from XML variable DECLARE @x XML SELECT @x = ' <Peoples> <people Name= "Tudou" sex= "female"/> <people name= "Choushuigou" sex= "female"/> <people name= "Dongsheng" sex= "Men"/> </P Eoples> ' SELECT v.value (' @Name [1] ', ' VARCHAR ') as Name, V.value (' @Sex [1] ', ' VARCHAR ') as Sex from @x.nodes ('/ Peoples/people ') x (v)--7. Multi-attribute filter DECLARE @x XML SELECT @x = ' <Employees> <employee id= ' 1234 ' dept= ' IT ' type= ' contract contractor "> <info name=" Dongsheng "sex=" male "qq=" 5454545454 "/> </Employee> <employee id=" 5656 "dept=" IT "type=" Temporary workers "> <info name=" Potato "sex=" qq= "5345454554"/> </Employee> <employee id= "3242" dept= "Market" type= "contract" > <info name= "Choushuigou" sex= "qq=" 54543545 "/> </Employee> </Employees>"--query dept for IT personnel Information-
  Method 1 SELECT c.value (' @NAME [1] ', ' VARCHAR ') as NAME, C.value (' @SEX [1] ', ' VARCHAR ') as SEX,  C.value (' @QQ [1] ', ' VARCHAR ') as QQ from @x.nodes ('/employees/employee[@dept = ' IT ']/info ') T (C)/* NAME SEX 
    QQ----------------------------------------dongsheng male 5454545454 Potato female 5345454554 * *-Method 2 SELECT C.value (' @NAME [1] ', ' VARCHAR ') as NAME, C.value (' @SEX [1] ', ' VARCHAR ') as SEX, C.value (' @QQ [1] ', ' VARCHAR (m) as QQ from @x.nodes ('//employee[@dept = ' IT ']/* ') T (C)/* NAME SEX QQ-------------------------------- --------Dongsheng male 5454545454 Potato female 5345454554/--Query The IT department type is permanent employee SELECT c.value (' @NAME [1] ', ' VARCHAR ') as NAME, C.value (' @SEX [1] ', ' VARCHAR ') as SEX, C.value (' @QQ [1] ', ' VARCHAR ') as QQ from @x.nodes (' employee[@dept = "IT"] [@type = "Contract contractor"]/* ') T (C)/* NAME SEX QQ----------------------------------------dongshen G Man 5454545454/--12. To delete an element from an XML variable DECLARE @x XML SELECT @x = ' <Peoples> <People> <NAME> potato </ name> <SEX> Male</SEX> <QQ>5345454554</QQ> </People> </Peoples> ' SET @x.modify (' Delete/peoples/peop Le/sex) [1] ') SELECT @x/* <Peoples> <People> <NAME> potato </NAME> <qq>5345454554</qq&gt
 ; </People> </Peoples> *--19. Reads the value of the specified variable element DECLARE @x XML SELECT @x = ' <Peoples> <People> <na
   Me>dongsheng</name> <SEX> men </SEX> <QQ>423545</QQ> </People> <People>
   <NAME> potatoes </NAME> <SEX> men </SEX> <QQ>123133</QQ> </People> <People> <NAME>choushuigou</NAME> <SEX> Women </SEX> <QQ>54543545</QQ> </People> < /peoples> ' DECLARE @ElementName VARCHAR Select @ElementName = ' name ' Select C.value ('. ', ' VARCHAR ') as NAME FRO
M @x.nodes ('/peoples/people/*[local-name () =sql:variable ("@ElementName")] T (C)/* Name-------------------- Dongsheng Potato Choushuigou * *--20 useWildcard Read element value--Reads the value of the root element DECLARE @x1 XML Select @x1 = ' <People>dongsheng</People> ' select @x1. Value (/*/text ()) [1 ] ', ' VARCHAR ') as people--asterisk * represents an element/* people--------------------Dongsheng/-Reads the value of the second-level element DECLARE @x XML SELECT @x = ' <People> <NAME>dongsheng</NAME> <SEX> man </SEX> <QQ>423545</QQ> < /people> ' SELECT @x.value (' (/*/*/text ()) [1] ', ' VARCHAR ') as name * * Name--------------------Dongsheng/-Read the second The value of the child element DECLARE @x XML SELECT @x = ' <People> <NAME>dongsheng</NAME> <SEX> man </SEX> & Lt Qq>423545</qq> </People> ' SELECT @x.value (' (/*/*/text ()) [2] ', ' VARCHAR ') as SEX/* SEX------------- -------Man/*-Read all second-level child element values DECLARE @x XML SELECT @x = ' <People> <NAME>dongsheng</NAME> <sex& GT </SEX> <QQ>423545</QQ> </People> ' SELECT c.value ('. ', ' VARCHAR ') as value from @x.nod Es ('/*/* ') T (C)/* value
--------------------Dongsheng male 423545/--21. Use wildcard characters to read element names DECLARE @x XML SELECT @x = ' &LT;PEOPLE&GT;DONGSHENG&LT;/PEOPL E> ' SELECT @x.value (' Local-name (/*[1) ', ' VARCHAR ') as elementname/* elementname--------------------people * * -Read the name and value of the first element under the root DECLARE @x XML SELECT @x = ' <People> <NAME>dongsheng</NAME> <SEX> male </se X> </People> ' SELECT @x.value (' Local-name (/*/*) [1]) ', ' VARCHAR ') as ElementName, @x.value (' (/*/*/text ()         ) [1] ', ' VARCHAR ') as Elementvalue/* elementname elementvalue----------------------------------------NAME Dongsheng * *-Read the name and value of the second element under the root DECLARE @x XML SELECT @x = ' <People> <NAME>dongsheng</NAME> ; sex> </SEX> </People> ' SELECT @x.value (' Local-name (/*/*) [2]) ', ' VARCHAR ') as ElementName, @x.valu E (' (/*/*/text ()) [2] ', ' VARCHAR ') as Elementvalue/* elementname elementvalue------------------------------------ ----SEX Male * *-readAll element names and values under the root DECLARE @x XML SELECT @x = ' <People> <NAME>dongsheng</NAME> <SEX> male </sex&gt
 ; </People> ' SELECT c.value (' Local-name (.) ', ' VARCHAR ') as ElementName, C.value ('. ', ' VARCHAR ') as Elementv Alue from @x.nodes ('/*/* ') T (C)/* elementname elementvalue----------------------------------------NAME Don
Gsheng SEX male/---22. Query the number of elements--below peoples there is a people child node under the root node. DECLARE @x XML SELECT @x = ' <Peoples> <People> <NAME>dongsheng</NAME> <SEX> male </sex > </People> <People> <NAME> potato </NAME> <SEX> men </SEX> </People> <peo ple> <NAME>choushuigou</NAME> <SEX> Women </SEX> </People> </Peoples> ' SELECT @x
. Value (' Count (/peoples/people) ', ' INT ') as Children/* Children-----------3 * *--as follows peoples the first child node under the root node people the number of sub nodes SELECT @x.value (' Count (/peoples/people[1]/*) ', ' INT ') as Children/* Children-----------2 * *-sometimes we may not know the root node and the name of the child node, you can use wildcard characters instead. SELECT @x.value (' Count (/*/*) ', ' int ') as Childrenofroot, @x.value (' Count (/*/*[1]/*) ', ' int ') as Childrenoffirstchildel Ement/* childrenofroot childrenoffirstchildelement-----------------------------------------3 2 * *--23. Number of query Properties D Eclare @x XML SELECT @x = ' <employees dept= "IT" > <employee name= "Dongsheng" sex= "male" qq= "5454545454"/> ; Employee name= "Potato" sex= "female" qq= "5345454554" tel= "13954697895"/> </Employees> "--Query and node attributes number SELECT @x.value (' Count (/employees/@*) ', ' INT ') as Attributecountofroot/* attributecountofroot--------------------1 * *-
Number of attributes for the first Employee node SELECT @x.value (' Count (/employees/employee[1]/@*) ', ' INT ') as Attributecountoffirstelement/* Attributecountoffirstelement----------------------------3/--Number of attributes for the second employee node SELECT @x.value (' Count (/ employees/employee[2]/@*) ', ' INT ') as attributecountofseconfelement/* Attributecountofseconfelement--------------- --------------4 * *-If you don't know the name of the sectionSay you can use the * wildcard character instead of SELECT @x.value (' Count (/*/@*) ', ' int ') as Attributecountofroot, @x.value (' Count (/*/*[1]/@*) ', ' int ') as Attr
Ibutecountoffirstelement, @x.value (' Count (/*/*[2]/@*) ', ' INT ') as Attributecountofseconfelement/* Attributecountofroot attributecountoffirstelement attributecountofseconfelement--------------------------------- --------------------------------------------1 3 4 * *-Returns the attribute value of no node SELECT c.value (' count (./@*) ', ' I NT ') as Attributecount from @x.nodes ('/*/* ') T (C)/* Attributecount--------------3 4 * *--24. Returns the value of the property or the name of the given position DECLARE @x x ML SELECT @x = ' <employees dept= "IT" > <employee name= "Dongsheng" sex= "male" qq= "5454545454"/> <employee N Ame= "Potato" sex= "female" qq= "5345454554" tel= "13954697895"/> </Employees> "--Returns the property value of the first position of the first employee node SELECT @
X.value (' (/employees/employee[1]/@*[position () =1]) [1] ', ' VARCHAR ') as Attvalue/* Attvalue-------------------- Dongsheng/-Returns the property value of the fourth position of the second employee node SELECT @x.value (' (/employeEs/employee[2]/@*[position () =4]) [1] ', ' VARCHAR ') as Attvalue/* Attvalue--------------------13954697895 * *- Returns the third property value of the first element SELECT @x.value (' Local-name (/employees/employee[1]/@*[position () =3]) [1]) ', ' VARCHAR ') as AttName/* AttName--------------------QQ */-Returns the fourth property value of the second element SELECT @x.value (' Local-name (/employees/employee[2]/@*[ Position () =4]) [1]) ', ' VARCHAR ' as AttName/* AttName--------------------TEL/-Returns the property value DECLARE @Elepos INT through the variable pass position, @Attpos INT Select @Elepos =2, @Attpos = 3 Select @x.value (' Local-name (/employees/employee[sql:variable ("@Elepos")]/@* [Position () =sql:variable ("@Attpos")]) [1]) as AttName/* AttName--------------------QQ/--25. Judge whether there is a corresponding property in the XML DECLARE @x xml SELECT @x = ' &L T  Employee name= "Potato" sex= "female" qq= "5345454554" tel= "13954697895"/> ' IF @x.exist ('/employee/@NAME ') = 1 SELECT ' Exists ' as Result ELSE SELECT ' does not exist ' as result * result------Exists/--pass variable to determine whether there is DECLARE @x XML Select @x = ' < Employee name="Potato" sex= "female" qq= "5345454554" tel= "13954697895"/> ' DECLARE @att VARCHAR () SELECT @att = ' QQ ' IF @x.exist ('/employee/@* [Local-name () =sql:variable ("@att")] = 1 Select ' Exists ' as result ELSE select ' does not exist ' as result/* result- -----Exists/--26. Iterate through all the attributes of the element DECLARE @x XML SELECT @x = ' <employee name= ' potato ' sex= ' female ' qq= ' 5345454554 ' tel= ' 13954697  895 "/> ' DECLARE @cnt int, @totCnt int, @attName VARCHAR (), @attValue VARCHAR () SELECT @cnt = 1, @totCnt 
      = @x.value (' Count (/employee/@*) ', ' INT ')--Gets the total number of attributes--loop while @cnt <= @totCnt BEGIN SELECT @attName = @x.value (
      ' Local-name ((/employee/@*[position () =sql:variable ("@cnt")]) [1]) ', ' VARCHAR ', @attValue = @x.value (  ' (/employee/@*[position () =sql:variable ("@cnt")]) [1] ', ' VARCHAR ') PRINT ' Attribute position: ' + CAST (@cnt As VARCHAR) print ' attribute Name: ' + @attName print ' attribute Value: ' + @attValue print '--increment the C Ounter variable SELECT @cnt = @cnt + 1 End/* attribute Position:1 name:name attribute Value: Potato attribute Position:2 attribute Nam E:sex attribute Value: female attribute position:3 attribute name:qq attribute value:5345454554 attribute Position:4 Attri
  Bute Name:tel Attribute value:13954697895 * *--27. Returns the child element for the specified location DECLARE @x XML SELECT @x = ' <employees dept= ' IT > <employee name= "Dongsheng" sex= "Men" qq= "5454545454"/> <employee "name=" "sex=" qq= "5345454554" 13954697895 "/> </Employees> ' SELECT @x.query (' (/employees/employee) [1] ')/* <employee name=" Dongsheng " sex= "male" qq= "5454545454"/>/select @x.query (' (/employees/employee) [Position () =2] ')/* <employee name= "Potato sex=" Female "qq=" 5345454554 "tel=" 13954697895 "/>"--get the child element of the specified position through a variable DECLARE @i INT Select @i = 2 Select @x.query (' (/employees/e Mployee) [Sql:variable ("@i")]--or SELECT @x.query (' (/employees/employee) [Position () =sql:variable ("@i")]]/* < Employee name= "Potato" sex= "female" qq= "5345454554" tel= "13954697895 "/> * *--28. Loop traversal get all child elements DECLARE @x XML SELECT @x = ' <employees dept= ' IT ' > <employee name= ' dongshe ng "sex=" male "qq=" 5454545454 "/> <employee name=" potato "sex=" female "qq=" 5345454554 "tel=" 13954697895 "/> </Employees&
gt; ' DECLARE @cnt int, @totCnt int, @child XML--counter variables SELECT @cnt = 1, @totCnt = @x.value (' Count (/emplo Yees/employee) ', ' INT ')--loop while @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) PRINT "--Incremet the counter variable SELECT @cnt = @cnt + 1 End/* Processing child Elem Ent:1 child element: <employee name= "Dongsheng" sex= "male" qq= "5454545454"/> Processing child element:2 child element

 : <employee name= "potato" sex= "female" qq= "5345454554" tel= "13954697895"/>

Five basic operations of XML data in SQL Server

1.xml.exist
Enter an XQuery expression that returns either 0,1 or null. 0 means not present, 1 indicates existence, NULL indicates NULL input
2.xml.value
Enter an XQuery expression that returns a SQL Server scalar value
3.xml.query
Enter an XQuery expression that returns a SQL Server XML type stream
4.xml.nodes
Enter an XQuery expression that returns a set of rows for an XML-formatted document
5.xml.modify

Insert, update, and delete operations on XML nodes using XQuery expressions.

The following examples illustrate 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 ')-----return 1
Select @XMLVar. exist ('/catalog/book1 ')-----Returns the 0
set @XMLVar = null
select @XMLVar. exist ('/catalog/book ')---- -Returns 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) ')

The result set is:
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 set is:

<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 Xmlvar.nodes ('/catalog/book ') as T (c)

The result set is:

<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 on DECLARE @x XML SELECT @x = ' <Peoples> <People> <emai l>1dongsheng@xxyy.com</email> <Phone>678945546</Phone> <QQ>36575</QQ> <add r>36575</addr> </People> </Peoples> '--Method 1 Select 1001 as Peopleid, p.* from (' C.value 
L-name (.) ', ' VARCHAR ') as Attrname, C.value ('. ', ' VARCHAR ') as AttrValue from @x.nodes ('/*/*/* ') T (C)--third layer) as P /* 1001 Email 1dongsheng@xxyy.com 1001 Phone 678945546 1001 QQ 36575 1001 Addr 36575 * * 

/*
 parse 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 ') as Attrname,
        c.value ('. ', ' VARCHAR ') as AttrValue from
    @xmlParas. Nodes (' + @layerstr + ') T (C)
    As P '
  --print @sql
   EXECUTE sp_executesql @sql, N ' @xmlParas as XML ', @xmlParas =@x end

DECLARE @x XML
SELECT @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 with your SQL Server database program.