sql XML操作

-=======

--XML

--=======

 

--1.modify()

DECLARE @x XML

SELECT @x = '<People NAME="dongsheng" SEX="女"/>'

DECLARE @SEX NVARCHAR(10)

SELECT @SEX = '男'

SET @x.modify('    replace value of (/People/@SEX)[1]     with sql:variable("@SEX")')

SELECT @x

 

/*output:<People NAME="dongsheng" SEX="男" />*/

 

 

--2.delete()

DECLARE @x XML

SELECT @x = '<People NAME="dongsheng" SEX="男"/>'

SET @x.modify('    delete (/People/@SEX)[1] ')

SELECT @x

/*output:<People NAME="dongsheng" />*/

 

 

--3.insert() attribute

 

DECLARE @x XML

SELECT @x = '<People NAME="dongsheng" />'

DECLARE @SEX VARCHAR(15)

SELECT @SEX = '男'

SET @x.modify('    insert attribute SEX {sql:variable("@SEX")} as last into   

(/People)[1]')

SELECT @x

/*output:<People NAME="dongsheng" SEX="男" />*/

 

--4.insert() element

DECLARE @x XML

SELECT @x ='<People NAME="dongsheng" />'

DECLARE @SEX VARCHAR(15)

SELECT @SEX = '男'

SET @x.modify('    insert element SEX {sql:variable("@SEX")} as last into   

(/People)[1]')

SELECT @x

/*

output:    <People NAME="dongsheng">

         <SEX>男</SEX>

       </People>

*/

 

 

 

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

 

/*

Name                 Sex

-------------------- --------------------

tudou                女

choushuigou          女

dongsheng            男

 

(3 行受影响) 

*/

 

 

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

 

*/

 

--8.通过变量定位和修改属性

 

 

DECLARE @x XML

SELECT @x = '

<Root>

  <Variables>

    <Variable VariableName="V1" Value="1" />

    <Variable VariableName="V2" Value="2" />

    <Variable VariableName="V3" Value="3" />

  </Variables>

</Root>'

 

DECLARE @var VARCHAR(20)

DECLARE @val VARCHAR(20)

 

SELECT @var = 'V3'--需要修改的Variable

SELECT @val = '6' --需要修改的值

 

--定位并且修改

SET @x.modify('

    replace value of (

        /Root/Variables/Variable[@VariableName=sql:variable("@var")]/@Value 

    )[1]

    with sql:variable("@val")

')

 

SELECT @x

 

/*

<Root>

  <Variables>

    <Variable VariableName="V1" Value="1" />

    <Variable VariableName="V2" Value="2" />

    <Variable VariableName="V3" Value="6" />--此处VALUE值被修改

  </Variables>

</Root>

*/

 

--9.上面演示如何修改XML变量,下面看看如何修改XML列

DECLARE @t TABLE (data XML)

INSERT INTO @t (data) SELECT '

<Root>

  <Variables>

    <Variable VariableName="V1" Value="1" />

    <Variable VariableName="V2" Value="2" />

    <Variable VariableName="V3" Value="3" />

  </Variables>

</Root>'

 

DECLARE @var VARCHAR(20)

DECLARE @val VARCHAR(20)

 

SELECT @var = 'V3'

SELECT @val = '6'

 

UPDATE @t

SET data.modify('

    replace value of (/Root/Variables/Variable[@VariableName=sql:variable("@var")]/@Value)[1]

    with sql:variable("@val")

')

 

SELECT * FROM @t

 

/*

<Root>

  <Variables>

    <Variable VariableName="V1" Value="1" />

    <Variable VariableName="V2" Value="2" />

    <Variable VariableName="V3" Value="6" />

  </Variables>

</Root>

*/

 

--10.上面根据属性值来定位更新,下面根据属性名称来定位更新

 

DECLARE @x XML

SELECT @x = '

<Peoples>

    <People NAME="土豆" SEX="女" QQ="5345454554"/>

</Peoples>'

 

DECLARE @attributename VARCHAR(20)

DECLARE @SEX VARCHAR(2)

 

SELECT @attributename = 'SEX'--需要定位的属性名称

SELECT @SEX = '男'         --属性需要更新的新值

 

 

SET @x.modify('

    replace value of (

        /Peoples/People/@*[local-name()=sql:variable("@attributename")]

    )[1]

    with sql:variable("@SEX")

')

 

select @x

 

/*

<Peoples>

  <People NAME="土豆" SEX="男" QQ="5345454554" />

</Peoples>

*/

 

 

 

--11.更新元素的值

DECLARE @x XML

SELECT @x = '

<Peoples>

  <People>

      <NAME>土豆</NAME>

      <SEX>男</SEX>

      <QQ>5345454554</QQ>

  </People>

</Peoples>'

 

DECLARE @SEX CHAR(2)

SELECT @SEX = '女'

 

SET @x.modify('

    replace value of (/Peoples/People/SEX/text())[1]

    with sql:variable("@SEX")' )

 

SELECT @x

/*

<Peoples>

  <People>

    <NAME>土豆</NAME>

    <SEX>女</SEX>

    <QQ>5345454554</QQ>

  </People>

</Peoples>

*/

 

 

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

*/

 

 

--13.XML运用分割字符串

 

-- create table

DECLARE @companies Table(   

    CompanyID INT,   

    CompanyCodes VARCHAR(100)

)

 

-- insert data

insert into @companies(CompanyID, CompanyCodes) values(1,'1|2')

insert into @companies(CompanyID, CompanyCodes) values(2,'1|2|3|4')

insert into @companies(CompanyID, CompanyCodes) values(3,'1|2')

 

-- Query

;WITH cte AS (

    SELECT

        CompanyID,

        CAST('<i>' + REPLACE(CompanyCodes, '|', '</i><i>') + '</i>' AS XML) AS CompanyCodes

    FROM @Companies

)

SELECT

    CompanyID,

    x.i.value('.', 'VARCHAR(10)') AS CompanyCode

FROM cte

CROSS APPLY CompanyCodes.nodes('//i') x(i)

 

/*

CompanyID   CompanyCode

----------- -----------

1           1         

1           2         

2           1         

2           2         

2           3         

2           4         

3           1         

3           2     

*/

 

 

--14.解析对应元素的前一个元素

DECLARE @x XML

SELECT @x = '

<Peoples>

  <People NAME="土豆"/>

  <People NAME="chouliumang"/>

  <People NAME="xiaobiesan"/>

</Peoples>'

select @x.query('

((/Peoples/People)[. << (/Peoples/People[@NAME="xiaobiesan"])[1]])[last()]')

/*

<People NAME="chouliumang" />

*/

 

--15.解析对应元素的下一个元素

DECLARE @x XML

SELECT @x = '

<Peoples>

  <People NAME="土豆"/>

  <People NAME="chouliumang"/>

  <People NAME="xiaobiesan"/>

</Peoples>'

 

    select @x.query('

    ((/Peoples/People)[. >> (/Peoples/People[@NAME="土豆"])[1]])[1]')

 

/*

<People NAME="chouliumang" />

*/

 

 

 

 

 

--16.移动元素

DECLARE @x XML

SELECT @x = '

<Peoples>

  <People NAME="土豆"/>

  <People NAME="chouliumang"/>

  <People NAME="xiaobiesan"/>

</Peoples>'

 

 

------------------------------------------------------------

-- "xiaobiesan" 向上移动一层

------------------------------------------------------------

set @x.modify('

    insert /Peoples/People[@NAME="xiaobiesan"]

    before (/Peoples/People[. << (/Peoples/People[@NAME="xiaobiesan"])[1]])

    [last()]

    ')

 

SET @x.modify ('

        delete /Peoples/People[@NAME="xiaobiesan"]

        [. is (/Peoples/People[@NAME="xiaobiesan"])[last()]]

    ')

 

SELECT @x

/*

<Peoples>

  <People NAME="土豆" />

  <People NAME="xiaobiesan" />

  <People NAME="chouliumang" />

</Peoples>

*/

 

------------------------------------------------------------

--  "土豆" 向下移动一层

------------------------------------------------------------

set @x.modify('

    insert /Peoples/People[@NAME="土豆"]

    before (/Peoples/People[. >> (/Peoples/People[@NAME="土豆"])[1]])

    [last()]

    ')

 

SET @x.modify ('

        delete (/Peoples/People[@NAME="土豆"])[1]

    ')

 

SELECT @x

/*

<Peoples>

  <People NAME="xiaobiesan" />

  <People NAME="土豆" />

  <People NAME="chouliumang" />

</Peoples>

*/

 

 

 

--17.移动属性到对应的元素前

 

--移动元素

DECLARE @x XML

SELECT @x = '

<Peoples>

  <People NAME="土豆"/>

  <People NAME="chouliumang"/>

  <People NAME="xiaobiesan"/>

  <People NAME="chunlv"/>

</Peoples>'

 

 

------------------------------------------------------------

-- "xiaobiesan" 向上移动到"土豆"前面

------------------------------------------------------------

set @x.modify('

    insert /Peoples/People[@NAME="xiaobiesan"]

    before (/Peoples/People[@NAME="土豆"])[1]

    ')

 

SET @x.modify ('

        delete (/Peoples/People[@NAME="xiaobiesan"] )[2]

    ')

 

SELECT @x

/*

<Peoples>

  <People NAME="xiaobiesan" />

  <People NAME="土豆" />

  <People NAME="chouliumang" />

  <People NAME="chunlv" />

</Peoples>

*/

 

------------------------------------------------------------

--  "土豆" 向下移动到"chunlv"后面

------------------------------------------------------------

set @x.modify('

    insert /Peoples/People[@NAME="土豆"]

    after (/Peoples/People[@NAME="chunlv"])[1]

    ')

 

SET @x.modify ('

        delete (/Peoples/People[@NAME="土豆"])[1]

    ')

 

SELECT @x

/*

<Peoples>

  <People NAME="xiaobiesan" />

  <People NAME="chouliumang" />

  <People NAME="chunlv" />

  <People NAME="土豆" />

</Peoples>

*/

 

 

--移动元素到指定的位置

------------------------------------------------------------

--  "xiaobiesan" 移动到position 3

------------------------------------------------------------

 

set @x.modify('

    insert /Peoples/People[@NAME="xiaobiesan"]

    after (/Peoples/People)[3]

    ')

 

SET @x.modify ('

        delete (/Peoples/People[@NAME="xiaobiesan"])[1]

    ')

 

SELECT @x

/*

<Peoples>

  <People NAME="chouliumang" />

  <People NAME="chunlv" />

  <People NAME="xiaobiesan" />

  <People NAME="土豆" />

</Peoples>

*/

 

------------------------------------------------------------

--  "chouliumang" 移动到最后(last())

------------------------------------------------------------

 

set @x.modify('

    insert /Peoples/People[@NAME="chouliumang"]

    after (/Peoples/People)[last()]

    ')

 

SET @x.modify ('

        delete (/Peoples/People[@NAME="chouliumang"])[1]

    ')

 

SELECT @x

 

/*

<Peoples>

  <People NAME="chunlv" />

  <People NAME="xiaobiesan" />

  <People NAME="土豆" />

  <People NAME="chouliumang" />

</Peoples>

*/

 

--18.查询元素的位置和值

DECLARE @x XML

SELECT @x = '

<Peoples>

  <People NAME="土豆"/>

  <People NAME="chouliumang"/>

  <People NAME="xiaobiesan"/>

  <People NAME="chunlv"/>

</Peoples>'

 

 

SELECT a.number as position

,b.name

FROM master.dbo.spt_values A

CROSS APPLY (

SELECT C.value('@NAME','varchar(20)') as  NAME

FROM @x.nodes('/Peoples/People[position()=sql:column("number")]')T(C)) b

 

WHERE A.type='P'

 

/*

position    name

----------- --------------------

1           土豆

2           chouliumang

3           xiaobiesan

4           chunlv

 

(4 行受影响)

*/

 

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

posted @ 2010-07-28 18:48  桃花雪  阅读(5642)  评论(0编辑  收藏  举报