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

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

--5.读取XML
--下面为多种方法从XML中读取EMAIL
DECLARE @x XML
SELECt @x = '

  
    dongsheng@xxyy.com
    678945546
    36575
  
'
-- 方法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)
  
  --方法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"]
--查询出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="合同工"]
--12.从XML变量中删除元素
DECLARE @x XML
SELECt @x = '

 
   土豆
   男
   5345454554
 
'
SET @x.modify('
  delete (/Peoples/People/SEX)[1]'
 )
SELECT @x

--19.读取指定变量元素的值
DECLARE @x XML
SELECT @x = '

 
   dongsheng
   男
   423545
 
 
   土豆
   男
   123133
 
 
   choushuigou
   女
   54543545
 

'
DECLARE @ElementName VARCHAr(20)
SELECT @ElementName = 'NAME'
SELECT c.value('.','VARCHAr(20)') AS NAME
FROM @x.nodes('/Peoples/People
--20使用通配符读取元素值
--读取根元素的值
DECLARE @x1 XML
SELECt @x1 = 'dongsheng'
SELECT @x1.value('(
--读取第二层元素的值
DECLARE  @x XML
SELECT @x = '
 
   dongsheng
   男
   423545
 '
SELECT
  @x.value('(text())[1]','VARCHAr(20)') AS NAME

--读取第二个子元素的值
DECLARE  @x XML
SELECT @x = '
 
   dongsheng
   男
   423545
 '
SELECT
  @x.value('(text())[2]','VARCHAr(20)') AS SEX

--读取所有第二层子元素值
DECLARE  @x XML
SELECT @x = '
 
   dongsheng
   男
   423545
 '
SELECT
  C.value('.','VARCHAr(20)') AS value
FROM @x.nodes('
--21.使用通配符读取元素名称
DECLARE @x XML
SELECt @x = 'dongsheng'
SELECT
  @x.value('local-name(
--读取根下第一个元素的名称和值
DECLARE  @x XML
SELECT @x = '
 
   dongsheng
   男
 '
SELECT
  @x.value('local-name((*/text())[1]','VARCHAr(20)') AS ElementValue

--读取根下第二个元素的名称和值
DECLARE  @x XML
SELECT @x = '
 
   dongsheng
   男
 '
SELECT
  @x.value('local-name((*/text())[2]','VARCHAr(20)') AS ElementValue

--读取根下所有的元素名称和值
DECLARE  @x XML
SELECT @x = '
 
   dongsheng
   男
 '
SELECT
  C.value('local-name(.)','VARCHAr(20)') AS ElementName,
  C.value('.','VARCHAr(20)') AS ElementValue
FROM @x.nodes('
---22.查询元素数量
--如下Peoples根节点下有个People子节点。
DECLARE @x XML
SELECt @x = '

 
   dongsheng
   男
 
 
   土豆
   男
 
 
   choushuigou
   女
 

'
SELECT  @x.value('count(/Peoples/People)','INT') AS Children

--如下Peoples根节点下第一个子节点People下子节点的数量
SELECT  @x.value('count(/Peoples/People[1]
--某些时候我们可能不知道根节点和子节点的名称，可以用通配符来代替。
SELECT  @x.value('count(*[1]
--23.查询属性的数量
DECLARE @x XML
SELECT @x = '

  
  
'
--查询跟节点的属性数量
SELECT  @x.value('count(/Employees/@*)','INT') AS AttributeCountOfRoot

--第一个Employee节点的属性数量
SELECT  @x.value('count(/Employees/Employee[1]/@*)','INT') AS AttributeCountOfFirstElement

--第二个Employee节点的属性数量
SELECT  @x.value('count(/Employees/Employee[2]/@*)','INT') AS AttributeCountOfSeconfElement

--如果不清楚节点名称可以用*通配符代替
SELECT  @x.value('count(*[1]/@*)','INT') AS AttributeCountOfFirstElement
    ,@x.value('count(
--返回没个节点的属性值
SELECT  C.value('count(./@*)','INT') AS AttributeCount
FROM @x.nodes('
--24.返回给定位置的属性值或者名称
DECLARE @x XML
SELECt @x = '

  
  
'
--返回第一个Employee节点的第一个位置的属性值
SELECT  @x.value('(/Employees/Employee[1]/@*[position()=1])[1]','VARCHAr(20)') AS AttValue

--返回第二个Employee节点的第四个位置的属性值
SELECT  @x.value('(/Employees/Employee[2]/@*[position()=4])[1]','VARCHAr(20)') AS AttValue

--返回第一个元素的第三个属性值
SELECT  @x.value('local-name((/Employees/Employee[1]/@*[position()=3])[1])','VARCHAr(20)') AS AttName

--返回第二个元素的第四个属性值
SELECT  @x.value('local-name((/Employees/Employee[2]/@*[position()=4])[1])','VARCHAr(20)') AS AttName

--通过变量传递位置返回属性值
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

--25.判断是XML中否存在相应的属性
DECLARE  @x XML
SELECT @x = ''
IF @x.exist('/Employee/@NAME') = 1
  SELECT 'Exists' AS Result
ELSE
  SELECT 'Does not exist' AS Result

--传递变量判断是否存在
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

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

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


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.com
    678945546
    36575
    36575

'
EXECUTE sp_ExportXml @x,'*'

希望本文所述对大家SQL Server数据库程序设计有所帮助。

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

MsSql相关栏目本月热门文章