这里有新鲜出炉的 SQL Server 教程,程序狗速度看过来!
SQL 是英文 Structured Query Language 的缩写,意思为结构化查询语言。SQL 语言的主要功能就是同各种数据库建立联系,进行沟通。按照 ANSI(美国国家标准协会) 的规定,SQL 被作为关系型数据库管理系统的标准语言。
SQL Server 即 Microsoft SQL Server 。
这篇文章主要介绍了 SQL Server 解析 XML 数据的方法, 结合实例形式详细分析了 SQL Server 针对 xml 数据的读取, 遍历, 删除, 查找等常用操作技巧, 具有一定参考借鉴价值, 需要的朋友可以参考下
本文实例讲述了 SQL Server 解析 XML 数据的方法。分享给大家供大家参考,具体如下:
- --5.读取XML
- --下面为多种方法从XML中读取EMAIL
- DECLARE @x XML
- SELECT @x = '
- <People>
- <dongsheng>
- <Info Name="Email">dongsheng@xxyy.com</Info>
- <Info Name="Phone">678945546</Info>
- <Info Name="qq">36575</Info>
- </dongsheng>
- </People>'
- -- 方法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 =
- '<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 Sex
- FROM @x.nodes('/Peoples/People') x(v)
- --7.多属性过滤
- DECLARE @x XML
- SELECT @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 QQ
- FROM @x.nodes('//Employee[@dept="IT"][@type="合同工"]/*') T(C)
- /*
- NAME SEX QQ
- ---------- ---------- --------------------
- dongsheng 男 5454545454
- */
- --12.从XML变量中删除元素
- DECLARE @x XML
- SELECT @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 XML
- SELECT @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 NAME
- FROM @x.nodes('/Peoples/People/*[local-name()=sql:variable("@ElementName")]') T(C)
- /*
- NAME
- --------------------
- dongsheng
- 土豆
- choushuigou
- */
- --20使用通配符读取元素值
- --读取根元素的值
- DECLARE @x1 XML
- SELECT @x1 = '<People>dongsheng</People>'
- SELECT @x1.value('(/*/text())[1]','VARCHAR(20)') AS People --星号*代表一个元素
- /*
- People
- --------------------
- dongsheng
- */
- --读取第二层元素的值
- DECLARE @x XML
- SELECT @x = '
- <People>
- <NAME>dongsheng</NAME>
- <SEX>男</SEX>
- <QQ>423545</QQ>
- </People>'
- SELECT
- @x.value('(/*/*/text())[1]','VARCHAR(20)') AS NAME
- /*
- NAME
- --------------------
- dongsheng
- */
- --读取第二个子元素的值
- DECLARE @x XML
- SELECT @x = '
- <People>
- <NAME>dongsheng</NAME>
- <SEX>男</SEX>
- <QQ>423545</QQ>
- </People>'
- SELECT
- @x.value('(/*/*/text())[2]','VARCHAR(20)') AS SEX
- /*
- SEX
- --------------------
- 男
- */
- --读取所有第二层子元素值
- DECLARE @x XML
- SELECT @x = '
- <People>
- <NAME>dongsheng</NAME>
- <SEX>男</SEX>
- <QQ>423545</QQ>
- </People>'
- SELECT
- C.value('.','VARCHAR(20)') AS value
- FROM @x.nodes('/*/*') T(C)
- /*
- value
- --------------------
- dongsheng
- 男
- 423545
- */
- --21.使用通配符读取元素名称
- DECLARE @x XML
- SELECT @x = '<People>dongsheng</People>'
- SELECT
- @x.value('local-name(/*[1])','VARCHAR(20)') AS ElementName
- /*
- ElementName
- --------------------
- People
- */
- --读取根下第一个元素的名称和值
- DECLARE @x XML
- SELECT @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 XML
- SELECT @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 XML
- SELECT @x = '
- <People>
- <NAME>dongsheng</NAME>
- <SEX>男</SEX>
- </People>'
- 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.查询元素数量
- --如下Peoples根节点下有个People子节点。
- DECLARE @x XML
- SELECT @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 XML
- SELECT @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 AttributeCount
- FROM @x.nodes('/*/*') T(C)
- /*
- AttributeCount
- --------------
- 3
- 4
- */
- --24.返回给定位置的属性值或者名称
- DECLARE @x XML
- SELECT @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
- --------------------
- */
- --返回第二个元素的第四个属性值
- 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
- --------------------
- */
- --25.判断是XML中否存在相应的属性
- DECLARE @x XML
- SELECT @x = '<Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895"/>'
- IF @x.exist('/Employee/@NAME') = 1
- SELECT 'Exists' AS Result
- ELSE
- SELECT 'Does not exist' AS Result
- /*
- Result
- ------
- Exists
- */
- --传递变量判断是否存在
- DECLARE @x XML
- SELECT @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 Result
- ELSE
- SELECT 'Does not exist' AS Result
- /*
- Result
- ------
- Exists
- */
- --26.循环遍历元素的所有属性
- DECLARE @x XML
- SELECT @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')--获得属性总数量
- -- 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 = '
- <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 INT
- SELECT @i = 2
- SELECT @x.query('(/Employees/Employee)[sql:variable("@i")]')
- --or
- SELECT @x.query('(/Employees/Employee)[position()=sql:variable("@i")]')
- /*
- <Employee NAME="土豆" SEX="女" QQ="5345454554" TEL="13954697895" />
- */
- --28.循环遍历获得所有子元素
- DECLARE @x XML
- SELECT @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 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: <Employee NAME="dongsheng" SEX="男" QQ="5454545454"/>
- Processing Child Element: 2
- Child 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')-----返回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)')
结果集为:
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 on
- DECLARE @x XML
- SELECT @x = '<Peoples>
- <People>
- <Email>1dongsheng@xxyy.com</Email>
- <Phone>678945546</Phone>
- <QQ>36575</QQ>
- <Addr>36575</Addr>
- </People>
- </Peoples>'
- -- 方法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 =
- '<Peoples>
- <People>
- <Email>1dongsheng@xxyy.com</Email>
- <Phone>678945546</Phone>
- <QQ>36575</QQ>
- <Addr>36575</Addr>
- </People>
- </Peoples>'
- EXECUTE sp_ExportXml @x,'/*/*/*'
希望本文所述对大家 SQL Server 数据库程序设计有所帮助。
来源: http://www.phperz.com/article/17/0819/339307.html