sql server XML中value(),exists(),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)
  1. /*------------------------------------------------------------------------------+   
  2. #| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = :  |   
  3. #|{>/------------------------------------------------------------------------<}|   
  4. #|: | Author     :    小爱                                                      |   
  5. #|: | Description:    XQuery的 value() 方法、 exist() 方法 和 nodes() 方法        |  
  6. #|: | SQL Version:    适用于 SQL 2005, SQL 2008 , SQL 2012                      |   
  7. #|: | Copyright  :    转载请注明出处。更多请访问：http://blog.csdn.net/beirut     |  
  8. #|: | Create Date:    2012-11-22                                                |  
  9. #|: | About Me   :    一个菜鸟dba                                               |  
  10. #|{>------------------------------------------------------------------------/<}|   
  11. #| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = :  |   
  12. #+-----------------------------------------------------------------------------*/   
  13. /*  
  14. T-SQL 支持用于查询 XML 数据类型的 XQuery 语言的子集。  
  15. 本章将分析XQuery的 value() 方法、 exist() 方法 和 nodes() 方法  
  16. */  
  17. ------------------------------value() 方法--------------------------------------  
  18. --value(XPath条件,数据类型)：结果为指定的标量值类型; XPath条件结果必须唯一  
  19.   
  20.   
  21. DECLARE @x XML   
  22. SET @x='  
  23. <root>  
  24.   <rogue id="001">  
  25.     <hobo id="1">  
  26.       <name>彪</name>  
  27.       <nickname>阿彪</nickname>  
  28.       <type>流氓</type>  
  29.     </hobo>  
  30.   </rogue>  
  31.   <rogue id="002">  
  32.     <hobo id="2">  
  33.       <name>光辉</name>  
  34.       <nickname>二辉</nickname>  
  35.       <type>流氓</type>  
  36.     </hobo>  
  37.   </rogue>  
  38.   <rogue id="001">  
  39.     <hobo id="3">  
  40.       <name>小德</name>  
  41.       <nickname>小D</nickname>  
  42.       <type>臭流氓</type>  
  43.     </hobo>  
  44.   </rogue>  
  45. </root>'  
  46. --value() 方法从 XML 中检索 rogue 属性值。然后将该值分配给 int 变量。  
  47.   
  48. SELECT @x.value('(/root/rogue/@id)[1]','int')  
  49. --解析 hobo 中属性 id 为2 的所有元素值  
  50. SELECT @x.value('(/root/rogue[2]/hobo/@id)[1]','int')  
  51. , @x.value('(/root/rogue[2]/hobo/name)[1]','varchar(10)')  
  52. , @x.value('(/root/rogue[2]/hobo/nickname)[1]','varchar(10)')  
  53. , @x.value('(/root/rogue[2]/hobo/type)[1]','varchar(10)')  
  54.   
  55.   
  56.   
  57. ------------------------------exist() 方法--------------------------------------  
  58. --exist() 方法- 用来判断 XQuery 表达式返回的结果是否为空  
  59.   
  60. ----判断 hobo 中属性 id  的值 是否为空  
  61.   
  62. SELECT @x.exist('(/root/rogue/hobo/@id)[1]')  
  63. --判断指定节点值是否相等  
  64. DECLARE @xml XML ='<root><name>a</name></root>'  
  65. SELECT @xml.exist('(/root/name[text()[1]="a"])')  
  66.   
  67. --用 exist() 方法比较日期时，请注意下列事项：  
  68. --代码 cast as xs:date? 用于将值转换为 xs:date 类型，以进行比较。  
  69. --@Somedate 属性的值是非类型化的。比较时，此值将隐式转换为比较右侧的类型（xs:date 类型）。  
  70. --可以使用 xs:date() 构造函数，而不用 cast as xs:date()。  
  71. DECLARE @a XML  
  72. SET @a='<root Somedate = "2012-01-01Z"/>'  
  73. SELECT @a.exist('/root[(@Somedate cast as xs:date?) eq xs:date("2012-01-01")]')  
  74. --下面的示例与上一示例类似，不同之处在于它具有 <Somedate> 元素。  
  75. SET @a = '<Somedate>2002-01-01Z</Somedate>'  
  76. SELECT  @a.exist('/Somedate[(text()[1] cast as xs:date ?) = xs:date("2002-01-01") ]')  
  77.    
  78. DECLARE    @x1 XML  
  79. SELECT @x1 = '<Employee Number="1001" Name="Jacob"/>'  
  80.   
  81. DECLARE @att VARCHAR(20)  
  82. SELECT @att = 'Number'  
  83.   
  84. IF @x1.exist('/Employee/@*[local-name()=sql:variable("@att")]') = 1   
  85.     SELECT 'Exists' AS Result  
  86. ELSE  
  87.     SELECT 'Does not exist' AS Result  
  88.   
  89. ------------------------------nodes() 方法--------------------------------------  
  90. --语法   
  91. --nodes (XQuery) as Table(Column) 将一个 XQuery 表达式拆分成多行  
  92. --以便于拆分成关系数据  
  93.    
  94. --将 rogue 节点拆分成多行  
  95. SELECT T.c.query('.') AS result  
  96. FROM   @x.nodes('/root/rogue') T(c);  
  97.   
  98. --扩展 rogue 拆分成数据行  
  99. SELECT T.c.value('(@id)[1]','varchar(10)') AS id  
  100. ,T.c.value('(./hobo/name)[1]','varchar(10)') AS name  
  101. ,T.c.value('(./hobo/nickname)[1]','varchar(10)') AS nickname  
  102. ,T.c.value('(./hobo/type)[1]','varchar(10)') AS type  
  103. FROM   @x.nodes('/root/rogue') T(c);  
  104.   
  105. /**********************************************************  
  106. *  
  107. * value() 方法 nodes() 方法 exist() 方法的综合应用  
  108. *  
  109. **********************************************************/  
  110.   
  111. --1 像下面的脚本，结点下还会用结点的，就要用到 text()  
  112. DECLARE @xml XML=N'   
  113. <a/>   
  114. <b>b<c>c</c></b>';  
  115. SELECT @xml.value('(/b)[1]', 'varchar(10)'), @xml.value('(/b/text())[1]', 'varchar(10)')  
  116.   
  117. --2 对表中的 XML 数据进行解析, 节点下面有多个相同节点的 使用 CROSS APPLY 和 nodes() 方法解析  
  118. IF OBJECT_ID('tempdb..[#tb]') IS NOT NULL DROP TABLE [#tb]   
  119. CREATE TABLE [#tb]([id] INT,[name] XML)   
  120. INSERT [#tb]   
  121. SELECT 1,'<r><i>a</i><i>b</i></r>' UNION ALL   
  122. SELECT 2,'<r><i>b</i></r>' UNION ALL   
  123. SELECT 3,'<r><i>d</i></r>'   
  124. SELECT   
  125.         T.c.query('.'),   
  126.         T.c.value('.', 'sysname')   
  127. FROM [#tb] A   
  128.         CROSS APPLY A.name.nodes('/r/i') T(c)  
  129.           
  130. --3 利用xml 拆分字符串  
  131. DECLARE @s VARCHAR(100)='1,2,3,4,5,6'  
  132. SELECT t.c.value('.','int') AS col  from    
  133. (SELECT CAST('<x>'+REPLACE(@s,',','</x><x>')+'</x>' AS XML ).query('.') AS name) AS a  
  134. CROSS APPLY a.name.nodes('/x') T(c)  
  135.   
  136.   
  137. --4 取任意属性的属性值，这里引入了 sql:variable  
  138. DECLARE @xml XML  
  139. DECLARE @Price DECIMAL(18 , 2),   
  140.         @xmlPath VARCHAR(10)= 'Price2'  
  141. 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"/>'  
  142. SELECT  col.value('(@Price2)[1]' , 'varchar(80)')  
  143. FROM    @xml.nodes('/row') data(col)  
  144.   
  145. SELECT  @xml.value('(/row/@*[local-name()=sql:variable( "@xmlPath")])[1]' , 'DECIMAL(18,2)')   
  146.   
  147. SELECT  col.value('(@Price2)[1]' , 'varchar(80)')  
  148. FROM    @xml.nodes('/row') data(col)  
  149.   
  150. SELECT  col.value('(@*[local-name()=sql:variable("@xmlPath")])[1]' , 'varchar(80)')  
  151. FROM    @xml.nodes('/row') data(col)  
  152. --组合使用  
  153. DECLARE    @x1 XML  
  154. SELECT @x1 = '  
  155. <Employees Dept="IT">  
  156.   <Employee Number="1001" Name="Jacob"/>  
  157.   <Employee Number="1002" Name="Bob" ReportsTo="Steve"/>  
  158. </Employees>'  
  159.   
  160. DECLARE @pos INT  
  161. SELECT @pos = 2  
  162.   
  163. SELECT  
  164.     @x1.value('local-name(  
  165.         (/Employees/Employee[2]/@*[position()=sql:variable("@pos")])[1]  
  166.     )','VARCHAR(20)') AS AttName  
  167.       
  168.   
  169. --5 使用 WITH XMLNAMESPACES  声明前缀 以及 XQuery 函数 namespace-uri()   
  170. DECLARE @xml XML  
  171. SELECT @xml = '  
  172. <employee  
  173. xmlns="http://schemas.microsoft.com/sqlserver/emp"  
  174. xmlns:loc="http://schemas.microsoft.com/sqlserver/location"  
  175. name="Jacob" position="CTO"  
  176. loc:city="Ahmedabad" loc:state="Gujarat" loc:country="India"  
  177. />'  
  178. --下面表达式将返回其命名空间 URI 为空的所有元素节点  
  179. --定义默认的命名空间  
  180. SELECT  
  181. x.value('local-name(.)', 'VARCHAR(20)') AS Attribute,  
  182. x.value('.', 'VARCHAR(20)') AS Value  
  183. FROM @xml.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/emp";   
  184. /employee/@*[namespace-uri()=""]') a(x)  
  185. --or  直接用通用符  
  186.   
  187. SELECT  
  188. x.value('local-name(.)', 'VARCHAR(20)') AS Attribute,  
  189. x.value('.', 'VARCHAR(20)') AS Value  
  190. FROM @xml.nodes('/*/@*[namespace-uri()=""]') a(x)  
  191.   
  192. -- 使用 WITH XMLNAMESPACES  
  193. ;WITH XMLNAMESPACES(  
  194. DEFAULT 'http://schemas.microsoft.com/sqlserver/emp'  
  195. )  
  196. SELECT  
  197. x.value('local-name(.)', 'VARCHAR(20)') AS Attribute,  
  198. x.value('.', 'VARCHAR(20)') AS Value  
  199. FROM @xml.nodes('/employee/@*[namespace-uri()=""]') a(x)  
  200.   
  201. --返回所有带有前缀的节点  
  202. ;WITH XMLNAMESPACES(  
  203. DEFAULT 'http://schemas.microsoft.com/sqlserver/emp'  
  204. )  
  205. SELECT  
  206. x.value('local-name(.)', 'VARCHAR(20)') AS Attribute,  
  207. x.value('.', 'VARCHAR(20)') AS Value  
  208. FROM @xml.nodes('/employee/@*[namespace-uri()="http://schemas.microsoft.com/sqlserver/location"]') a(x)  
  209.   
  210. --返回所有带有前缀的节点个数统计  
  211. ;WITH XMLNAMESPACES(  
  212. DEFAULT 'http://schemas.microsoft.com/sqlserver/emp'  
  213. )  
  214. SELECT @xml.value('count(/employee/@*[namespace-uri()="http://schemas.microsoft.com/sqlserver/location"])', 'int') AS [count]  
  215.   
  216. --sql:column() 函数  
  217. --将普通数据列和 xml 数据列进行合并  
  218.   
  219.   
  220. DECLARE @t TABLE (id INT , data XML)  
  221.    
  222. INSERT  INTO @t (id , data)  
  223.         SELECT  1 , '<root><name>二辉</name><type>流氓</type></root>'  
  224.         UNION ALL  
  225.         SELECT  2 , '<root><name>彪</name><type>流氓</type></root>'  
  226. SELECT  id ,   
  227. data=data.query('<root>  
  228.     <id>{sql:column("id")}</id>  
  229.     {/root/name}  
  230.     {/root/type}  
  231.     </root>')  
  232. FROM    @t  
  233.   
  234. /*  
  235.   
  236. id          data  
  237. ----------- ----------------------------------------------------  
  238. 1   <root><id>1</id><name>二辉</name><type>流氓</type></root>  
  239. 2   <root><id>2</id><name>彪</name><type>流氓</type></root>  
  240.   
  241. */  
  242.   
  243. --根据一个xml 变量 与表中的值进行关联查询  
  244.   
  245. DECLARE @tb TABLE (id INT)  
  246. INSERT INTO @tb(id)   
  247. SELECT 1 UNION ALL   
  248. SELECT 2 UNION ALL   
  249. SELECT 3  
  250.   
  251. declare @XmlData xml   
  252. set  @XmlData = '  
  253. <root>  
  254. <rogue><typeid>1</typeid><name>二辉</name><type>流氓</type></rogue>  
  255. <rogue><typeid>2</typeid><name>彪</name><type>流氓</type></rogue>  
  256. </root>'  
  257.   
  258. SELECT t.id AS id  
  259. FROM @tb t  
  260. CROSS APPLY @XmlData.nodes('/root/rogue/typeid[. = sql:column("id")]') a(x)  
  261.   
  262.   
  263.   
  264. --string-length() 函数 和 number() 函数  
  265. --提取长度为5的数字  
  266. DECLARE @t TABLE (CustomerID INT, CustomerAddress VARCHAR(50))  
  267. INSERT INTO @t(CustomerID, CustomerAddress)   
  268.     SELECT 1, '12 20 97TH STREET NEW GARDENS, NY  11415  APT 8P' UNION ALL  
  269.     SELECT 2, '20-10 93RD STREET #8A VICTORIA NY 11106 19TH FLR' UNION ALL  
  270.     SELECT 3, '290 BERKELEY STREET APT24D  NYC, NY  10038' UNION ALL  
  271.     SELECT 4, '351-250  345 STREET PANAMA BEACH 11414  APT4F'  
  272.       
  273. ;WITH cte AS (  
  274.     SELECT   
  275.         CustomerID,  
  276.         CAST('<i>' +REPLACE(CustomerAddress, ' ', '</i><i>') + '</i>' AS XML).query('.') AS CustomerAddress  
  277.     FROM @t  
  278. )  
  279. SELECT   
  280.     CustomerID,  
  281.     x.i.value('.', 'VARCHAR(10)') AS ZipCode  
  282. FROM cte  
  283. CROSS APPLY CustomerAddress.nodes('//i[string-length(.)=5][number()>0]') x(i)  
  284.       
  285. /*  
  286. CustomerID  ZipCode  
  287. ----------- ----------  
  288. 1           11415  
  289. 2           11106  
  290. 3           10038  
  291. 4           11414  
  292. */  
  293.   
  294. --使用 contains() 函数进行模糊查询  
  295. SELECT  a.x.value('name[1]' , 'varchar(10)')  
  296. FROM    @x.nodes('/root/rogue/hobo') a (x)  
  297. CROSS APPLY a.x.nodes('type[contains(.,"臭流氓")]') b (y)  
  298.   
  299. SELECT  t.c.query('.')  
  300. FROM    @x.nodes('/root/rogue/hobo[./type[contains(.,"臭流氓")]]') t (c)  
立刻行动，坚持不懈，不断学习！
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/deng779256146/p/8637304.html