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">[email protected]</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.通过变量定位和修改属性