当前位置:数据库 > > 正文

sqlserver 存储过程(SQL Server解析XML数据的方法详解)

时间:2022-03-30 09:47:05类别:数据库

sqlserver 存储过程

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
    --------------------
    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 = '<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数据库程序设计有所帮助。

    标签:
    上一篇下一篇

    猜您喜欢

    热门推荐