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

转自：http://www.cnblogs.com/l1pe1/archive/2010/07/28/1787254.html