本文實例講述了SQL Server解析XML數(shù)據(jù)的方法。分享給大家供大家參考，具體如下：

--5.讀取XML
--下面為多種方法從XML中讀取EMAIL
DECLARE?@x?XML
SELECT?@x?=?'dongsheng@xxyy.com67894554636575'
--?方法1
SELECT?@x.value('data(/People/dongsheng/Info[@Name="Email"])[1]',?'varchar(30)')
--?方法2
SELECT?@x.value('(/People/dongsheng/Info[@Name="Email"])[1]',?'varchar(30)')
--?方法3
SELECT
??C.value('.','varchar(30)')
FROM?@x.nodes('/People/dongsheng/Info[@Name="Email"]')?T(C)
--?方法4
SELECT
??C.value('(Info[@Name="Email"])[1]','varchar(30)')
FROM?@x.nodes('/People/dongsheng')?T(C)
--?方法5
SELECT
??C.value('(dongsheng/Info[@Name="Email"])[1]','varchar(30)')
FROM?@x.nodes('/People')?T(C)
--?方法6
SELECT
??C.value('.','varchar(30)')
FROM?@x.nodes('/People/dongsheng/Info')?T(C)
WHERE?C.value('(.[@Name="Email"])[1]','varchar(30)')?IS?NOT?NULL
--?方法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?variable
DECLARE?@x?XML
SELECT?@x?=
''
SELECT
??v.value('@Name[1]','VARCHAR(20)')?AS?Name,
??v.value('@Sex[1]','VARCHAR(20)')?AS?Sex
FROM?@x.nodes('/Peoples/People')?x(v)
--7.多屬性過濾
DECLARE?@x?XML
SELECT?@x?=?''
--查詢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?QQ
FROM?@x.nodes('//Employee[@dept="IT"][@type="合同工"]/*')?T(C)
/*
??NAME???SEX????QQ
??----------?----------?--------------------
??dongsheng?男?????5454545454
*/
--12.從XML變量中刪除元素
DECLARE?@x?XML
SELECT?@x?=?'土豆男5345454554'
SET?@x.modify('
??delete?(/Peoples/People/SEX)[1]'
?)
SELECT?@x
/*土豆5345454554*/
--19.讀取指定變量元素的值
DECLARE?@x?XML
SELECT?@x?=?'dongsheng男423545土豆男123133choushuigou女54543545'
DECLARE?@ElementName?VARCHAR(20)
SELECT?@ElementName?=?'NAME'
SELECT?c.value('.','VARCHAR(20)')?AS?NAME
FROM?@x.nodes('/Peoples/People/*[local-name()=sql:variable("@ElementName")]')?T(C)
/*
NAME
--------------------
dongsheng
土豆
choushuigou
*/
--20使用通配符讀取元素值
--讀取根元素的值
DECLARE?@x1?XML
SELECT?@x1?=?'dongsheng'
SELECT?@x1.value('(/*/text())[1]','VARCHAR(20)')?AS?People?--星號*代表一個元素
/*
People
--------------------
dongsheng
*/
--讀取第二層元素的值
DECLARE??@x?XML
SELECT?@x?=?'dongsheng男423545'
SELECT
??@x.value('(/*/*/text())[1]','VARCHAR(20)')?AS?NAME
/*
NAME
--------------------
dongsheng
*/
--讀取第二個子元素的值
DECLARE??@x?XML
SELECT?@x?=?'dongsheng男423545'
SELECT
??@x.value('(/*/*/text())[2]','VARCHAR(20)')?AS?SEX
/*
SEX
--------------------
男
*/
--讀取所有第二層子元素值
DECLARE??@x?XML
SELECT?@x?=?'dongsheng男423545'
SELECT
??C.value('.','VARCHAR(20)')?AS?value
FROM?@x.nodes('/*/*')?T(C)
/*
value
--------------------
dongsheng
男
423545
*/
--21.使用通配符讀取元素名稱
DECLARE?@x?XML
SELECT?@x?=?'dongsheng'
SELECT
??@x.value('local-name(/*[1])','VARCHAR(20)')?AS?ElementName
/*
ElementName
--------------------
People
*/
--讀取根下第一個元素的名稱和值
DECLARE??@x?XML
SELECT?@x?=?'dongsheng男'
SELECT
??@x.value('local-name((/*/*)[1])','VARCHAR(20)')?AS?ElementName,
??@x.value('(/*/*/text())[1]','VARCHAR(20)')?AS?ElementValue
/*
ElementName?????ElementValue
--------------------?--------------------
NAME?????????dongsheng
*/
--讀取根下第二個元素的名稱和值
DECLARE??@x?XML
SELECT?@x?=?'dongsheng男'
SELECT
??@x.value('local-name((/*/*)[2])','VARCHAR(20)')?AS?ElementName,
??@x.value('(/*/*/text())[2]','VARCHAR(20)')?AS?ElementValue
/*
ElementName?????ElementValue
--------------------?--------------------
SEX?????????男
*/
--讀取根下所有的元素名稱和值
DECLARE??@x?XML
SELECT?@x?=?'dongsheng男'
SELECT
??C.value('local-name(.)','VARCHAR(20)')?AS?ElementName,
??C.value('.','VARCHAR(20)')?AS?ElementValue
FROM?@x.nodes('/*/*')?T(C)
/*
ElementName?????ElementValue
--------------------?--------------------
NAME?????????dongsheng
SEX?????????男
*/
---22.查詢元素數(shù)量
--如下Peoples根節(jié)點下有個People子節(jié)點。
DECLARE?@x?XML
SELECT?@x?=?'dongsheng男土豆男choushuigou女'
SELECT??@x.value('count(/Peoples/People)','INT')?AS?Children
/*
Children
-----------
3
*/
--如下Peoples根節(jié)點下第一個子節(jié)點People下子節(jié)點的數(shù)量
SELECT??@x.value('count(/Peoples/People[1]/*)','INT')?AS?Children
/*
Children
-----------
2
*/
--某些時候我們可能不知道根節(jié)點和子節(jié)點的名稱，可以用通配符來代替。
SELECT??@x.value('count(/*/*)','INT')?AS?ChildrenOfRoot,
?????@x.value('count(/*/*[1]/*)','INT')?AS?ChildrenOfFirstChildElement
/*
ChildrenOfRoot?ChildrenOfFirstChildElement
--------------?---------------------------
3???????2
*/
--23.查詢屬性的數(shù)量
DECLARE?@x?XML
SELECT?@x?=?''
--查詢跟節(jié)點的屬性數(shù)量
SELECT??@x.value('count(/Employees/@*)','INT')?AS?AttributeCountOfRoot
/*
AttributeCountOfRoot
--------------------
1
*/
--第一個Employee節(jié)點的屬性數(shù)量
SELECT??@x.value('count(/Employees/Employee[1]/@*)','INT')?AS?AttributeCountOfFirstElement
/*
AttributeCountOfFirstElement
----------------------------
3
*/
--第二個Employee節(jié)點的屬性數(shù)量
SELECT??@x.value('count(/Employees/Employee[2]/@*)','INT')?AS?AttributeCountOfSeconfElement
/*
AttributeCountOfSeconfElement
-----------------------------
4
*/
--如果不清楚節(jié)點名稱可以用*通配符代替
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
*/
--返回沒個節(jié)點的屬性值
SELECT??C.value('count(./@*)','INT')?AS?AttributeCount
FROM?@x.nodes('/*/*')?T(C)
/*
AttributeCount
--------------
3
4
*/
--24.返回給定位置的屬性值或者名稱
DECLARE?@x?XML
SELECT?@x?=?''
--返回第一個Employee節(jié)點的第一個位置的屬性值
SELECT??@x.value('(/Employees/Employee[1]/@*[position()=1])[1]','VARCHAR(20)')?AS?AttValue
/*
AttValue
--------------------
dongsheng
*/
--返回第二個Employee節(jié)點的第四個位置的屬性值
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?INT
SELECT?@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.判斷是XML中否存在相應(yīng)的屬性
DECLARE??@x?XML
SELECT?@x?=?''
IF?@x.exist('/Employee/@NAME')?=?1
??SELECT?'Exists'?AS?Result
ELSE
??SELECT?'Does?not?exist'?AS?Result
/*
Result
------
Exists
*/
--傳遞變量判斷是否存在
DECLARE??@x?XML
SELECT?@x?=?''
DECLARE?@att?VARCHAR(20)
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.循環(huán)遍歷元素的所有屬性
DECLARE??@x?XML
SELECT?@x?=?''
DECLARE
??@cnt?INT,
??@totCnt?INT,
??@attName?VARCHAR(30),
??@attValue?VARCHAR(30)
SELECT
??@cnt?=?1,
??@totCnt?=?@x.value('count(/Employee/@*)','INT')--獲得屬性總數(shù)量
--?loop
WHILE?@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?+?1
END
/*
Attribute?Position:?1
Attribute?Name:?NAME
Attribute?Value:?土豆
Attribute?Position:?2
Attribute?Name:?SEX
Attribute?Value:?女
Attribute?Position:?3
Attribute?Name:?QQ
Attribute?Value:?5345454554
Attribute?Position:?4
Attribute?Name:?TEL
Attribute?Value:?13954697895
*/
--27.返回指定位置的子元素
DECLARE?@x?XML
SELECT?@x?=?''
SELECT?@x.query('(/Employees/Employee)[1]')
/**/
SELECT?@x.query('(/Employees/Employee)[position()=2]')
/**/
--通過變量獲取指定位置的子元素
DECLARE?@i?INT
SELECT?@i?=?2
SELECT?@x.query('(/Employees/Employee)[sql:variable("@i")]')
--or
SELECT?@x.query('(/Employees/Employee)[position()=sql:variable("@i")]')
/**/
--28.循環(huán)遍歷獲得所有子元素
DECLARE?@x?XML
SELECT?@x?=?''
DECLARE
??@cnt?INT,
??@totCnt?INT,
??@child?XML
--?counter?variables
SELECT
??@cnt?=?1,
??@totCnt?=?@x.value('count(/Employees/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(100))
??PRINT?''
??--?incremet?the?counter?variable
??SELECT?@cnt?=?@cnt?+?1
END
/*
Processing?Child?Element:?1
Child?element:Processing?Child?Element:?2
Child?element:

SQL Server 中對XML數(shù)據(jù)的五種基本操作

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

使用XQuery表達(dá)式對XML的節(jié)點進(jìn)行insert , update 和 delete 操作。

下面通過例子對上面的五種操作進(jìn)行說明：

declare?@XMLVar?xml?=?'Windows?Step?By?StepBill?Zack49.99Developing?ADO?.NETAndrew?Brust39.93Windows?Cluster?ServerStephen?Forte59.99'

1.?xml.exist

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

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

結(jié)果集分別為：Windows?Step?By?StepBill?Zack49.99Developing?ADO?.NETAndrew?Brust39.93Windows?Cluster?ServerStephen?Forte59.99Windows?Step?By?StepBill?Zack49.99Andrew?Brust4.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)

結(jié)果集分別為：Windows?Step?By?StepBill?…………Developing?ADO?.NETAndrew?…………Windows?Cluster?ServerStephen?…………Windows?Step?By?StepDeveloping?ADO?.NETWindows?Cluster?Serverset?ARITHABORT?on
DECLARE?@x?XML
SELECT?@x?=?'1dongsheng@xxyy.com6789455463657536575'
--?方法1
select?1001?as?peopleId,?p.*?FROM(
SELECT
??C.value('local-name(.)','VARCHAR(20)')?AS?attrName,
??C.value('.','VARCHAR(20)')?AS?attrValue
FROM?@x.nodes('/*/*/*')?T(C)?--第三層
)?as?p
/*
1001??Email??1dongsheng@xxyy.com
1001??Phone??678945546
1001??QQ?36575
1001??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=@x
END
?
DECLARE?@x?XML
SELECT?@x?=
'1dongsheng@xxyy.com6789455463657536575'
EXECUTE?sp_ExportXml?@x,'/*/*/*'

希望本文所述對大家SQL Server數(shù)據(jù)庫程序設(shè)計有所幫助。