SQL Server XML基础学习之<5>--XQuery的 value() 方法、 exist() 方法 和 nodes() 方法
/*T-SQL 支持用于查询 XML 数据类型的 XQuery 语言的子集。本章将分析XQuery的 value() 方法、 exist() 方法 和 nodes() 方法*/------------------------------value() 方法----------------------------------------value(XPath条件,数据类型)：结果为指定的标量值类型; XPath条件结果必须唯一DECLARE @x XML SET @x='<root> <rogue id="001"> <hobo id="1"> <name>彪</name> <nickname>阿彪</nickname> <type>流氓</type> </hobo> </rogue> <rogue id="002"> <hobo id="2"> <name>光辉</name> <nickname>二辉</nickname> <type>流氓</type> </hobo> </rogue> <rogue id="001"> <hobo id="3"> <name>小德</name> <nickname>小D</nickname> <type>臭流氓</type> </hobo> </rogue></root>'--value() 方法从 XML 中检索 rogue 属性值。然后将该值分配给 int 变量。SELECT @x.value('(/root/rogue/@id)[1]','int')--解析 hobo 中属性 id 为2 的所有元素值SELECT @x.value('(/root/rogue[2]/hobo/@id)[1]','int'), @x.value('(/root/rogue[2]/hobo/name)[1]','varchar(10)'), @x.value('(/root/rogue[2]/hobo/nickname)[1]','varchar(10)'), @x.value('(/root/rogue[2]/hobo/type)[1]','varchar(10)')------------------------------exist() 方法----------------------------------------exist() 方法- 用来判断 XQuery 表达式返回的结果是否为空----判断 hobo 中属性 id 的值 是否为空SELECT @x.exist('(/root/rogue/hobo/@id)[1]')--判断指定节点值是否相等DECLARE @xml XML ='<root><name>a</name></root>'SELECT @xml.exist('(/root/name[text()[1]="a"])')--用 exist() 方法比较日期时，请注意下列事项：--代码 cast as xs:date? 用于将值转换为 xs:date 类型，以进行比较。--@Somedate 属性的值是非类型化的。比较时，此值将隐式转换为比较右侧的类型（xs:date 类型）。--可以使用 xs:date() 构造函数，而不用 cast as xs:date()。DECLARE @a XMLSET @a='<root Somedate = "2012-01-01Z"/>'SELECT @a.exist('/root[(@Somedate cast as xs:date?) eq xs:date("2012-01-01")]')--下面的示例与上一示例类似，不同之处在于它具有 <Somedate> 元素。SET @a = '<Somedate>2002-01-01Z</Somedate>'SELECT @a.exist('/Somedate[(text()[1] cast as xs:date ?) = xs:date("2002-01-01") ]') DECLARE @x1 XMLSELECT @x1 = '<Employee Number="1001" Name="Jacob"/>'DECLARE @att VARCHAR(20)SELECT @att = 'Number'IF @x1.exist('/Employee/@*[local-name()=sql:variable("@att")]') = 1 SELECT 'Exists' AS ResultELSE SELECT 'Does not exist' AS Result------------------------------nodes() 方法----------------------------------------语法 --nodes (XQuery) as Table(Column) 将一个 XQuery 表达式拆分成多行--以便于拆分成关系数据 --将 rogue 节点拆分成多行SELECT T.c.query('.') AS resultFROM @x.nodes('/root/rogue') T(c);--扩展 rogue 拆分成数据行SELECT T.c.value('(@id)[1]','varchar(10)') AS id,T.c.value('(./hobo/name)[1]','varchar(10)') AS name,T.c.value('(./hobo/nickname)[1]','varchar(10)') AS nickname,T.c.value('(./hobo/type)[1]','varchar(10)') AS typeFROM @x.nodes('/root/rogue') T(c);/************************************************************ value() 方法 nodes() 方法 exist() 方法的综合应用***********************************************************/--1 像下面的脚本，结点下还会用结点的，就要用到 text()DECLARE @xml XML=N' <a/> <b>b<c>c</c></b>';SELECT @xml.value('(/b)[1]', 'varchar(10)'), @xml.value('(/b/text())[1]', 'varchar(10)')--2 对表中的 XML 数据进行解析, 节点下面有多个相同节点的 使用 CROSS APPLY 和 nodes() 方法解析IF OBJECT_ID('tempdb..[#tb]') IS NOT NULL DROP TABLE [#tb] CREATE TABLE [#tb]([id] INT,[name] XML) INSERT [#tb] SELECT 1,'<r><i>a</i><i>b</i></r>' UNION ALL SELECT 2,'<r><i>b</i></r>' UNION ALL SELECT 3,'<r><i>d</i></r>' SELECT T.c.query('.'), T.c.value('.', 'sysname') FROM [#tb] A CROSS APPLY A.name.nodes('/r/i') T(c) --3 利用xml 拆分字符串DECLARE @s VARCHAR(100)='1,2,3,4,5,6'SELECT t.c.value('.','int') AS col from (SELECT CAST('<x>'+REPLACE(@s,',','</x><x>')+'</x>' AS XML ).query('.') AS name) AS aCROSS APPLY a.name.nodes('/x') T(c)--4 取任意属性的属性值，这里引入了 sql:variableDECLARE @xml XMLDECLARE @Price DECIMAL(18 , 2), @xmlPath VARCHAR(10)= 'Price2'SET @xml='<row ProductID="1" Price1="111.00" Price2="222.00" Price3="333.00" Price4="444.00" Price5="555.00" Price6="666.00" Price7="777.00"/>'SELECT col.value('(@Price2)[1]' , 'varchar(80)')FROM @xml.nodes('/row') data(col)SELECT @xml.value('(/row/@*[local-name()=sql:variable( "@xmlPath")])[1]' , 'DECIMAL(18,2)') SELECT col.value('(@Price2)[1]' , 'varchar(80)')FROM @xml.nodes('/row') data(col)SELECT col.value('(@*[local-name()=sql:variable("@xmlPath")])[1]' , 'varchar(80)')FROM @xml.nodes('/row') data(col)--组合使用DECLARE @x1 XMLSELECT @x1 = '<Employees Dept="IT"> <Employee Number="1001" Name="Jacob"/> <Employee Number="1002" Name="Bob" ReportsTo="Steve"/></Employees>'DECLARE @pos INTSELECT @pos = 2SELECT @x1.value('local-name( (/Employees/Employee[2]/@*[position()=sql:variable("@pos")])[1] )','VARCHAR(20)') AS AttName --5 使用 WITH XMLNAMESPACES 声明前缀 以及 XQuery 函数 namespace-uri() DECLARE @xml XMLSELECT @xml = '<employeexmlns="http://schemas.microsoft.com/sqlserver/emp"xmlns:loc="http://schemas.microsoft.com/sqlserver/location"name="Jacob" position="CTO"loc:city="Ahmedabad" loc:state="Gujarat" loc:country="India"/>'--下面表达式将返回其命名空间 URI 为空的所有元素节点--定义默认的命名空间SELECTx.value('local-name(.)', 'VARCHAR(20)') AS Attribute,x.value('.', 'VARCHAR(20)') AS ValueFROM @xml.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/emp"; /employee/@*[namespace-uri()=""]') a(x)--or 直接用通用符SELECTx.value('local-name(.)', 'VARCHAR(20)') AS Attribute,x.value('.', 'VARCHAR(20)') AS ValueFROM @xml.nodes('/*/@*[namespace-uri()=""]') a(x)-- 使用 WITH XMLNAMESPACES;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/emp')SELECTx.value('local-name(.)', 'VARCHAR(20)') AS Attribute,x.value('.', 'VARCHAR(20)') AS ValueFROM @xml.nodes('/employee/@*[namespace-uri()=""]') a(x)--返回所有带有前缀的节点;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/emp')SELECTx.value('local-name(.)', 'VARCHAR(20)') AS Attribute,x.value('.', 'VARCHAR(20)') AS ValueFROM @xml.nodes('/employee/@*[namespace-uri()="http://schemas.microsoft.com/sqlserver/location"]') a(x)--返回所有带有前缀的节点个数统计;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/emp')SELECT @xml.value('count(/employee/@*[namespace-uri()="http://schemas.microsoft.com/sqlserver/location"])', 'int') AS [count]--sql:column() 函数--将普通数据列和 xml 数据列进行合并DECLARE @t TABLE (id INT , data XML) INSERT INTO @t (id , data) SELECT 1 , '<root><name>二辉</name><type>流氓</type></root>' UNION ALL SELECT 2 , '<root><name>彪</name><type>流氓</type></root>'SELECT id , data=data.query('<root><id>{sql:column("id")}</id>{/root/name}{/root/type}</root>')FROM @t/*id data----------- ----------------------------------------------------1<root><id>1</id><name>二辉</name><type>流氓</type></root>2<root><id>2</id><name>彪</name><type>流氓</type></root>*/--根据一个xml 变量 与表中的值进行关联查询DECLARE @tb TABLE (id INT)INSERT INTO @tb(id) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3declare @XmlData xml set @XmlData = '<root><rogue><typeid>1</typeid><name>二辉</name><type>流氓</type></rogue><rogue><typeid>2</typeid><name>彪</name><type>流氓</type></rogue></root>'SELECT t.id AS idFROM @tb tCROSS APPLY @XmlData.nodes('/root/rogue/typeid[. = sql:column("id")]') a(x)--string-length() 函数 和 number() 函数--提取长度为5的数字DECLARE @t TABLE (CustomerID INT, CustomerAddress VARCHAR(50))INSERT INTO @t(CustomerID, CustomerAddress) SELECT 1, '12 20 97TH STREET NEW GARDENS, NY 11415 APT 8P' UNION ALL SELECT 2, '20-10 93RD STREET #8A VICTORIA NY 11106 19TH FLR' UNION ALL SELECT 3, '290 BERKELEY STREET APT24D NYC, NY 10038' UNION ALL SELECT 4, '351-250 345 STREET PANAMA BEACH 11414 APT4F' ;WITH cte AS ( SELECT CustomerID, CAST('<i>' +REPLACE(CustomerAddress, ' ', '</i><i>') + '</i>' AS XML).query('.') AS CustomerAddress FROM @t)SELECT CustomerID, x.i.value('.', 'VARCHAR(10)') AS ZipCodeFROM cteCROSS APPLY CustomerAddress.nodes('//i[string-length(.)=5][number()>0]') x(i) /*CustomerID ZipCode----------- ----------1 114152 111063 100384 11414*/--使用 contains() 函数进行模糊查询SELECT a.x.value('name[1]' , 'varchar(10)')FROM @x.nodes('/root/rogue/hobo') a (x)CROSS APPLY a.x.nodes('type[contains(.,"臭流氓")]') b (y)SELECT t.c.query('.')FROM @x.nodes('/root/rogue/hobo[./type[contains(.,"臭流氓")]]') t (c)