当前位置：首页 > 芯闻号 > 充电吧

SQL Server解析XML数据的方法详解

时间：2018-11-26 15:18:01
关键字： server    sql    解析xml   
手机看文章

扫描二维码
随时随地手机看文章

[导读]本文实例讲述了SQL Server解析XML数据的方法。分享给大家供大家参考，具体如下：--5.读取XML --下面为多种方法从XML中读取EMAIL DECLARE @x XML SELECT @x

本文实例讲述了SQL Server解析XML数据的方法。分享给大家供大家参考，具体如下：


--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.查询元素数量
--如下Peoples根节点下有个People子节点。
DECLARE @x XML
SELECT @x = 'dongsheng男土豆男choushuigou女'
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 = ''
--查询跟节点的属性数量
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 = ''
--返回第一个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 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中否存在相应的属性
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.循环遍历元素的所有属性
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')--获得属性总数量
-- 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.循环遍历获得所有子元素
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数据的五种基本操作

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

结果集为：
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')

结果集分别为：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)

结果集分别为：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数据库程序设计有所帮助。

本站声明： 本文章由作者或相关机构授权发布，目的在于传递更多信息，并不代表本站赞同其观点，本站亦不保证或承诺内容真实性等。需要转载请联系该专栏作者，如若文章内容侵犯您的权益，请及时联系本站删除。
换一批
延伸阅读
[通信先锋]

阿维塔、赛力斯已入股！华为引望可能成“中国博世”

9月2日消息，不造车的华为或将催生出更大的独角兽公司，随着阿维塔和赛力斯的入局，华为引望愈发显得引人瞩目。

关键字： 阿维塔 塞力斯 华为
[美通社全球TMT]

Trianz与AWS达成战略合作协议，彻底改变云采用和管理方式

加利福尼亚州圣克拉拉县2024年8月30日 /美通社/ -- 数字化转型技术解决方案公司Trianz今天宣布，该公司与Amazon Web Services （AWS）签订了...

关键字： AWS AN BSP 数字化
[美通社全球TMT]

人工智能驱动工具SODA V将颠覆汽车市场，使汽车开发时间和成本降低90%

伦敦2024年8月29日 /美通社/ -- 英国汽车技术公司SODA.Auto推出其旗舰产品SODA V，这是全球首款涵盖汽车工程师从创意到认证的所有需求的工具，可用于创建软件定义汽车。 SODA V工具的开发耗时1.5...

关键字： 汽车 人工智能 智能驱动 BSP
[美通社全球TMT]

从容应对未知风险----解密亚马逊云科技的韧性之道

北京2024年8月28日 /美通社/ -- 越来越多用户希望企业业务能7×24不间断运行，同时企业却面临越来越多业务中断的风险，如企业系统复杂性的增加，频繁的功能更新和发布等。如何确保业务连续性，提升韧性，成...

关键字： 亚马逊 解密 控制平面 BSP
[通信先锋]

中国游戏市场开始复苏！腾讯、网易等巨头缩减在日本投资

8月30日消息，据媒体报道，腾讯和网易近期正在缩减他们对日本游戏市场的投资。

关键字： 腾讯 编码器 CPU
[通信先锋]

独立自主！华为董事：致力打造不依赖西方的技术

8月28日消息，今天上午，2024中国国际大数据产业博览会开幕式在贵阳举行，华为董事、质量流程IT总裁陶景文发表了演讲。

关键字： 华为 12nm EDA 半导体
[通信先锋]

华为张平安：数字世界话语权最终由生态繁荣决定！

8月28日消息，在2024中国国际大数据产业博览会上，华为常务董事、华为云CEO张平安发表演讲称，数字世界的话语权最终是由生态的繁荣决定的。

关键字： 华为 12nm 手机 卫星通信
[美通社全球TMT]

中国通信服务公布2024年中期业绩

要点： 有效应对环境变化，经营业绩稳中有升 落实提质增效举措，毛利润率延续升势 战略布局成效显著，战新业务引领增长 以科技创新为引领，提升企业核心竞争力 坚持高质量发展策略，塑强核心竞争优势...

关键字： 通信 BSP 电信运营商 数字经济
[美通社全球TMT]

NVI技术创新联盟成立！自主生态将带动产业链高速发展

北京2024年8月27日 /美通社/ -- 8月21日，由中央广播电视总台与中国电影电视技术学会联合牵头组建的NVI技术创新联盟在BIRTV2024超高清全产业链发展研讨会上宣布正式成立。 活动现场 NVI技术创新联...

关键字： VI 传输协议 音频 BSP
[美通社全球TMT]

软通动力与长三角投资达成战略合作 共谋数字生态新发展

北京2024年8月27日 /美通社/ -- 在8月23日举办的2024年长三角生态绿色一体化发展示范区联合招商会上，软通动力信息技术（集团）股份有限公司（以下简称"软通动力"）与长三角投资（上海）有限...

关键字： BSP 信息技术
关闭
关闭