SQL Server解析XML資料的方法詳解_MsSql

本文執行個體講述了SQL Server解析XML資料的方法。分享給大家供大家參考，具體如下：

--5.讀取XML--下面為多種方法從XML中讀取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>'-- 方法1SELECT @x.value('data(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)')-- 方法2SELECT @x.value('(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)')-- 方法3SELECT  C.value('.','varchar(30)')FROM @x.nodes('/People/dongsheng/Info[@Name="Email"]') T(C)-- 方法4SELECT  C.value('(Info[@Name="Email"])[1]','varchar(30)')FROM @x.nodes('/People/dongsheng') T(C)-- 方法5SELECT  C.value('(dongsheng/Info[@Name="Email"])[1]','varchar(30)')FROM @x.nodes('/People') T(C)-- 方法6SELECT  C.value('.','varchar(30)')FROM @x.nodes('/People/dongsheng/Info') T(C)WHERE C.value('(.[@Name="Email"])[1]','varchar(30)') IS NOT NULL-- 方法7SELECT  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 ='<Peoples>  <People Name="tudou" Sex="女" />  <People Name="choushuigou" Sex="女"/>  <People Name="dongsheng" Sex="男" /></Peoples>'SELECT  v.value('@Name[1]','VARCHAR(20)') AS Name,  v.value('@Sex[1]','VARCHAR(20)') AS SexFROM @x.nodes('/Peoples/People') x(v)--7.多屬性過濾DECLARE @x XMLSELECT @x = '<Employees> <Employee id="1234" dept="IT" type="合約工">  <Info NAME="dongsheng" SEX="男" QQ="5454545454"/> </Employee> <Employee id="5656" dept="IT" type="臨時工">  <Info NAME="馬鈴薯" SEX="女" QQ="5345454554"/> </Employee> <Employee id="3242" dept="市場" type="合約工">  <Info NAME="choushuigou" SEX="女" QQ="54543545"/> </Employee></Employees>'--查詢dept為IT的人員資訊  --方法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 男     5454545454  馬鈴薯   女     5345454554  */  --方法2  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('//Employee[@dept="IT"]/*') T(C)  /*  NAME   SEX    QQ  ---------- ---------- --------------------  dongsheng 男     5454545454  馬鈴薯   女     5345454554  */--查詢出IT部門type為Permanent的員工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="合約工"]/*') T(C)/*  NAME   SEX    QQ  ---------- ---------- --------------------  dongsheng 男     5454545454*/--12.從XML變數中刪除元素DECLARE @x XMLSELECT @x = '<Peoples> <People>   <NAME>馬鈴薯</NAME>   <SEX>男</SEX>   <QQ>5345454554</QQ> </People></Peoples>'SET @x.modify('  delete (/Peoples/People/SEX)[1]' )SELECT @x/*<Peoples> <People>  <NAME>馬鈴薯</NAME>  <QQ>5345454554</QQ> </People></Peoples>*/--19.讀取指定變數元素的值DECLARE @x XMLSELECT @x = '<Peoples> <People>   <NAME>dongsheng</NAME>   <SEX>男</SEX>   <QQ>423545</QQ> </People> <People>   <NAME>馬鈴薯</NAME>   <SEX>男</SEX>   <QQ>123133</QQ> </People> <People>   <NAME>choushuigou</NAME>   <SEX>女</SEX>   <QQ>54543545</QQ> </People></Peoples>'DECLARE @ElementName VARCHAR(20)SELECT @ElementName = 'NAME'SELECT c.value('.','VARCHAR(20)') AS NAMEFROM @x.nodes('/Peoples/People/*[local-name()=sql:variable("@ElementName")]') T(C)/*NAME--------------------dongsheng馬鈴薯choushuigou*/--20使用萬用字元讀取元素值--讀取根項目的值DECLARE @x1 XMLSELECT @x1 = '<People>dongsheng</People>'SELECT @x1.value('(/*/text())[1]','VARCHAR(20)') AS People --星號*代表一個元素/*People--------------------dongsheng*/--讀取第二層元素的值DECLARE  @x XMLSELECT @x = ' <People>   <NAME>dongsheng</NAME>   <SEX>男</SEX>   <QQ>423545</QQ> </People>'SELECT  @x.value('(/*/*/text())[1]','VARCHAR(20)') AS NAME/*NAME--------------------dongsheng*/--讀取第二個子項目的值DECLARE  @x XMLSELECT @x = ' <People>   <NAME>dongsheng</NAME>   <SEX>男</SEX>   <QQ>423545</QQ> </People>'SELECT  @x.value('(/*/*/text())[2]','VARCHAR(20)') AS SEX/*SEX--------------------男*/--讀取所有第二層子項目值DECLARE  @x XMLSELECT @x = ' <People>   <NAME>dongsheng</NAME>   <SEX>男</SEX>   <QQ>423545</QQ> </People>'SELECT  C.value('.','VARCHAR(20)') AS valueFROM @x.nodes('/*/*') T(C)/*value--------------------dongsheng男423545*/--21.使用萬用字元讀取元素名稱DECLARE @x XMLSELECT @x = '<People>dongsheng</People>'SELECT  @x.value('local-name(/*[1])','VARCHAR(20)') AS ElementName/*ElementName--------------------People*/--讀取根下第一個元素的名稱和值DECLARE  @x XMLSELECT @x = ' <People>   <NAME>dongsheng</NAME>   <SEX>男</SEX> </People>'SELECT  @x.value('local-name((/*/*)[1])','VARCHAR(20)') AS ElementName,  @x.value('(/*/*/text())[1]','VARCHAR(20)') AS ElementValue/*ElementName     ElementValue-------------------- --------------------NAME         dongsheng*/--讀取根下第二個元素的名稱和值DECLARE  @x XMLSELECT @x = ' <People>   <NAME>dongsheng</NAME>   <SEX>男</SEX> </People>'SELECT  @x.value('local-name((/*/*)[2])','VARCHAR(20)') AS ElementName,  @x.value('(/*/*/text())[2]','VARCHAR(20)') AS ElementValue/*ElementName     ElementValue-------------------- --------------------SEX         男*/--讀取根下所有的元素名稱和值DECLARE  @x XMLSELECT @x = ' <People>   <NAME>dongsheng</NAME>   <SEX>男</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         男*/---22.查詢元素數量--如下Peoples根節點下有個People子節點。DECLARE @x XMLSELECT @x = '<Peoples> <People>   <NAME>dongsheng</NAME>   <SEX>男</SEX> </People> <People>   <NAME>馬鈴薯</NAME>   <SEX>男</SEX> </People> <People>   <NAME>choushuigou</NAME>   <SEX>女</SEX> </People></Peoples>'SELECT  @x.value('count(/Peoples/People)','INT') AS Children/*Children-----------3*/--如下Peoples根節點下第一個子節點People下子節點的數量SELECT  @x.value('count(/Peoples/People[1]/*)','INT') AS Children/*Children-----------2*/--某些時候我們可能不知道根節點和子節點的名稱，可以用萬用字元來代替。SELECT  @x.value('count(/*/*)','INT') AS ChildrenOfRoot,     @x.value('count(/*/*[1]/*)','INT') AS ChildrenOfFirstChildElement/*ChildrenOfRoot ChildrenOfFirstChildElement-------------- ---------------------------3       2*/--23.查詢屬性的數量DECLARE @x XMLSELECT @x = '<Employees dept="IT">  <Employee NAME="dongsheng" SEX="男" QQ="5454545454"/>  <Employee NAME="馬鈴薯" SEX="女" QQ="5345454554" TEL="13954697895"/></Employees>'--查詢跟節點的屬性數量SELECT  @x.value('count(/Employees/@*)','INT') AS AttributeCountOfRoot/*AttributeCountOfRoot--------------------1*/--第一個Employee節點的屬性數量SELECT  @x.value('count(/Employees/Employee[1]/@*)','INT') AS AttributeCountOfFirstElement/*AttributeCountOfFirstElement----------------------------3*/--第二個Employee節點的屬性數量SELECT  @x.value('count(/Employees/Employee[2]/@*)','INT') AS AttributeCountOfSeconfElement/*AttributeCountOfSeconfElement-----------------------------4*/--如果不清楚節點名稱可以用*萬用字元代替SELECT  @x.value('count(/*/@*)','INT') AS AttributeCountOfRoot    ,@x.value('count(/*/*[1]/@*)','INT') AS AttributeCountOfFirstElement    ,@x.value('count(/*/*[2]/@*)','INT') AS AttributeCountOfSeconfElement/*AttributeCountOfRoot AttributeCountOfFirstElement AttributeCountOfSeconfElement-------------------- ---------------------------- -----------------------------1          3              4*/--返回沒個節點的屬性值SELECT  C.value('count(./@*)','INT') AS AttributeCountFROM @x.nodes('/*/*') T(C)/*AttributeCount--------------34*/--24.返回給定位置的屬性值或者名稱DECLARE @x XMLSELECT @x = '<Employees dept="IT">  <Employee NAME="dongsheng" SEX="男" QQ="5454545454"/>  <Employee NAME="馬鈴薯" SEX="女" QQ="5345454554" TEL="13954697895"/></Employees>'--返回第一個Employee節點的第一個位置的屬性值SELECT  @x.value('(/Employees/Employee[1]/@*[position()=1])[1]','VARCHAR(20)') AS AttValue/*AttValue--------------------dongsheng*/--返回第二個Employee節點的第四個位置的屬性值SELECT  @x.value('(/Employees/Employee[2]/@*[position()=4])[1]','VARCHAR(20)') AS AttValue/*AttValue--------------------13954697895*/--返回第一個元素的第三個屬性值SELECT  @x.value('local-name((/Employees/Employee[1]/@*[position()=3])[1])','VARCHAR(20)') AS AttName/*AttName--------------------QQ*/--返回第二個元素的第四個屬性值SELECT  @x.value('local-name((/Employees/Employee[2]/@*[position()=4])[1])','VARCHAR(20)') AS AttName/*AttName--------------------TEL*/--通過變數傳遞位置返回屬性值DECLARE @Elepos INT,@Attpos INTSELECT @Elepos=2,@Attpos = 3SELECT  @x.value('local-name((/Employees/Employee[sql:variable("@Elepos")]/@*[position()=sql:variable("@Attpos")])[1])','VARCHAR(20)') AS AttName/*AttName--------------------QQ*/--25.判斷是XML中否存在相應的屬性DECLARE  @x XMLSELECT @x = '<Employee NAME="馬鈴薯" SEX="女" QQ="5345454554" TEL="13954697895"/>'IF @x.exist('/Employee/@NAME') = 1  SELECT 'Exists' AS ResultELSE  SELECT 'Does not exist' AS Result/*Result------Exists*/--傳遞變數判斷是否存在DECLARE  @x XMLSELECT @x = '<Employee NAME="馬鈴薯" SEX="女" QQ="5345454554" TEL="13954697895"/>'DECLARE @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"/>'DECLARE  @cnt INT,  @totCnt INT,  @attName VARCHAR(30),  @attValue VARCHAR(30)SELECT  @cnt = 1,  @totCnt = @x.value('count(/Employee/@*)','INT')--獲得屬性總數量-- 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: 1Attribute Name: NAMEAttribute Value: 馬鈴薯Attribute Position: 2Attribute Name: SEXAttribute Value: 女Attribute Position: 3Attribute Name: QQAttribute Value: 5345454554Attribute Position: 4Attribute Name: TELAttribute Value: 13954697895*/--27.返回指定位置的子項目DECLARE @x XMLSELECT @x = '<Employees dept="IT">  <Employee NAME="dongsheng" SEX="男" QQ="5454545454"/>  <Employee NAME="馬鈴薯" SEX="女" QQ="5345454554" TEL="13954697895"/></Employees>'SELECT @x.query('(/Employees/Employee)[1]')/*<Employee NAME="dongsheng" SEX="男" QQ="5454545454" />*/SELECT @x.query('(/Employees/Employee)[position()=2]')/*<Employee NAME="馬鈴薯" SEX="女" QQ="5345454554" TEL="13954697895" />*/--通過變數擷取指定位置的子項目DECLARE @i INTSELECT @i = 2SELECT @x.query('(/Employees/Employee)[sql:variable("@i")]')--orSELECT @x.query('(/Employees/Employee)[position()=sql:variable("@i")]')/*<Employee NAME="馬鈴薯" SEX="女" QQ="5345454554" TEL="13954697895" />*/--28.迴圈遍曆獲得所有子項目DECLARE @x XMLSELECT @x = '<Employees dept="IT">  <Employee NAME="dongsheng" SEX="男" QQ="5454545454"/>  <Employee NAME="馬鈴薯" SEX="女" QQ="5345454554" TEL="13954697895"/></Employees>'DECLARE  @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: 1Child element: <Employee NAME="dongsheng" SEX="男" QQ="5454545454"/>Processing Child Element: 2Child element: <Employee NAME="馬鈴薯" SEX="女" QQ="5345454554" TEL="13954697895"/>

SQL Server 中對XML資料的五種基本操作

1.xml.exist
   輸入為XQuery運算式，返回0，1或是Null。0表示不存在，1表示存在，Null表示輸入為空白
2.xml.value
   輸入為XQuery運算式，返回一個SQL Server標量值
3.xml.query
   輸入為XQuery運算式，返回一個SQL Server XML類型流
4.xml.nodes
   輸入為XQuery運算式，返回一個XML格式文檔的一列行集
5.xml.modify

使用XQuery運算式對XML的節點進行insert , update 和 delete 操作。

下面通過例子對上面的五種操作進行說明：

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')-----返回1select @XMLVar.exist('/catalog/book/@category')-----返回1select @XMLVar.exist('/catalog/book1')-----返回0set @XMLVar = nullselect @XMLVar.exist('/catalog/book')-----返回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)')

結果集為：
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')

結果集分別為：

<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)

結果集分別為：

<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 = '<Peoples><People>    <Email>1dongsheng@xxyy.com</Email>    <Phone>678945546</Phone>    <QQ>36575</QQ>    <Addr>36575</Addr></People></Peoples>'-- 方法1select 1001 as peopleId, p.* FROM(SELECT  C.value('local-name(.)','VARCHAR(20)') AS attrName,  C.value('.','VARCHAR(20)') AS attrValueFROM @x.nodes('/*/*/*') T(C) --第三層) as p/*1001  Email  1dongsheng@xxyy.com1001  Phone  6789455461001  QQ 365751001  Addr  36575*/

/* 解析XML預存程序*/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,'/*/*/*'

希望本文所述對大家SQL Server資料庫程式設計有所協助。