SQL Server XML基础学习之<6>--XQuery的 value() 方法、 exist() 方法 和 nodes() 方法

/*------------------------------------------------------------------------------+  
#| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = :  |  
#|{>/------------------------------------------------------------------------\<}|  
#|: | Author     :    小爱                                                      |  
#|: | Description:    XQuery的 value() 方法、 exist() 方法 和 nodes() 方法        | 
#|: | SQL Version:    适用于 SQL 2005, SQL 2008 , SQL 2012                      |  
#|: | Copyright  :    转载请注明出处。更多请访问： http://blog.csdn.net/beirut     | 
#|: | Create Date:    2012-11-22                                                | 
#|: | About Me   :    一个菜鸟dba                                               | 
#|{>\------------------------------------------------------------------------/<}|  
#| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = :  |  
#+-----------------------------------------------------------------------------*/  
/* 
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 XML 
SET @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 XML 
SELECT @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 Result 
ELSE 
    SELECT 'Does not exist' AS Result 
 
------------------------------nodes() 方法-------------------------------------- 
--语法  
--nodes (XQuery) as Table(Column) 将一个 XQuery 表达式拆分成多行 
--以便于拆分成关系数据 
  
--将 rogue 节点拆分成多行 
SELECT T.c.query('.') AS result 
FROM   @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 type 
FROM   @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 a 
CROSS APPLY a.name.nodes('/x') T(c) 
 
 
--4 取任意属性的属性值，这里引入了 sql:variable 
DECLARE @xml XML 
DECLARE @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 XML 
SELECT @x1 = ' 
<Employees Dept="IT"> 
  <Employee Number="1001" Name="Jacob"/> 
  <Employee Number="1002" Name="Bob" ReportsTo="Steve"/> 
</Employees>' 
 
DECLARE @pos INT 
SELECT @pos = 2 
 
SELECT 
    @x1.value('local-name( 
        (/Employees/Employee[2]/@*[position()=sql:variable("@pos")])[1] 
    )','VARCHAR(20)') AS AttName 
     
 
--5 使用 WITH XMLNAMESPACES  声明前缀 以及 XQuery 函数 namespace-uri()  
DECLARE @xml XML 
SELECT @xml = ' 
<employee 
xmlns=" 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 为空的所有元素节点 
--定义默认的命名空间 
SELECT 
x.value('local-name(.)', 'VARCHAR(20)') AS Attribute, 
x.value('.', 'VARCHAR(20)') AS Value 
FROM @xml.nodes('declare default element namespace " http://schemas.microsoft.com/sqlserver/emp";  
/employee/@*[namespace-uri()=""]') a(x) 
--or  直接用通用符 
 
SELECT 
x.value('local-name(.)', 'VARCHAR(20)') AS Attribute, 
x.value('.', 'VARCHAR(20)') AS Value 
FROM @xml.nodes( '/*/@*[namespace-uri()=""]') a(x) 
 
-- 使用 WITH XMLNAMESPACES 
;WITH XMLNAMESPACES( 
DEFAULT 'http://schemas.microsoft.com/sqlserver/emp' 
) 
SELECT 
x.value('local-name(.)', 'VARCHAR(20)') AS Attribute, 
x.value('.', 'VARCHAR(20)') AS Value 
FROM @xml.nodes( '/employee/@*[namespace-uri()=""]') a(x) 
 
--返回所有带有前缀的节点 
;WITH XMLNAMESPACES( 
DEFAULT 'http://schemas.microsoft.com/sqlserver/emp' 
) 
SELECT 
x.value('local-name(.)', 'VARCHAR(20)') AS Attribute, 
x.value('.', 'VARCHAR(20)') AS Value 
FROM @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 3 
 
declare @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 id 
FROM @tb t 
CROSS 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 ZipCode 
FROM cte 
CROSS APPLY CustomerAddress.nodes('//i[string-length(.)=5][number()>0]') x(i) 
     
/* 
CustomerID  ZipCode 
----------- ---------- 
1           11415 
2           11106 
3           10038 
4           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)