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

 --5.读取XML --下面为多种方法从XML中读取EMAIL DECLARE @x XML SELECT @x = ' dongsheng@xxyy.com67894554636575' -- 方法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) /* 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 = ' 土豆男5345454554' SET @x.modify(' delete (/Peoples/People/SEX)[1]' ) SELECT @x /* 土豆5345454554 */ --19.读取指定变量元素的值 DECLARE @x XML SELECT @x = ' dongsheng男423545土豆男123133choushuigou女54543545 ' 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 = 'dongsheng' SELECT @x1.value('(/*/text())[1]','VARCHAR(20)') AS People --星号*代表一个元素 /* People -------------------- dongsheng */ --读取第二层元素的值 DECLARE @x XML SELECT @x = ' dongsheng男423545' SELECT @x.value('(/*/*/text())[1]','VARCHAR(20)') AS NAME /* NAME -------------------- dongsheng */ --读取第二个子元素的值 DECLARE @x XML SELECT @x = ' dongsheng男423545' SELECT @x.value('(/*/*/text())[2]','VARCHAR(20)') AS SEX /* SEX -------------------- 男 */ --读取所有第二层子元素值 DECLARE @x XML SELECT @x = ' dongsheng男423545' SELECT C.value('.','VARCHAR(20)') AS value FROM @x.nodes('/*/*') T(C) /* value -------------------- dongsheng 男 423545 */ --21.使用通配符读取元素名称 DECLARE @x XML SELECT @x = 'dongsheng' SELECT @x.value('local-name(/*[1])','VARCHAR(20)') AS ElementName /* ElementName -------------------- People */ --读取根下第一个元素的名称和值 DECLARE @x XML SELECT @x = ' dongsheng男' 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 = ' dongsheng男' 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 = ' dongsheng男' 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 = ' dongsheng男土豆男choushuigou女 ' 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 = ' ' --查询跟节点的属性数量 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 = ' ' --返回第一个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 = '' IF @x.exist('/Employee/@NAME') = 1 SELECT 'Exists' AS Result ELSE SELECT 'Does not exist' AS Result /* Result ------ Exists */ --传递变量判断是否存在 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 /* Result ------ Exists */ --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 /* 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 = ' 提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                

                

                

                                            
上一篇：SQLSERVER简单创建DBLINK操作远程服务器数据库的方法_MsSql_
                                                                
下一篇：SQL Server简单实现数据的日报和月报功能_MsSql_