SQL Server XML基础学习之<5>—XQuery(query)

你的名字 2023-10-10 09:32 6阅读 0赞

T-SQL 支持用于查询 XML 数据类型的 XQuery 语言的子集。
 XQuery 基于现有的 XPath 查询语言，并支持更好的迭代、更好的排序结果以及构造必需的 XML 的功能。
 在前面我们已经学习了 XPath 的基本语法，本章将学习T-SQL 的 XQuery 语法
 ------------------------------------------------------------------------------------------------------------------ 
 T-SQL XQuery包含如下函数
 query(XPath条件)： 结果为 xml 类型; 返回由符合条件的节点组成的非类型化的 XML 实例
 value(XPath条件,数据类型)：结果为指定的标量值类型; xpath条件结果必须唯一
 exist(XPath条件)：结果为布尔值; 表示节点是否存在,如果执行查询的 XML 数据类型实例包含NULL则返回NULL
 nodes(XPath条件): 返回由符合条件的节点组成的一行一列的结果表
 */
 
 DECLARE @x XML 
 SET @x='
 <root>
 <rogue id="001">
 <hobo id="1">
 <name>彪</name>
 <name>阿彪</name>
 <type>流氓</type>
 </hobo>
 </rogue>
 <rogue id="002">
 <hobo id="2">
 <name>光辉</name>
 <name>二辉</name>
 <type>流氓</type>
 </hobo>
 </rogue>
 <rogue id="001">
 <hobo id="3">
 <name>小德</name>
 <name>小D</name>
 <type>臭流氓</type>
 </hobo>
 </rogue>
 </root>'
 
 --取root的所有子节点
 --SELECT @x.query('root'),@x.query('/root'),@x.query('.')
 
 --/*注释：
 -- 这里实际上是取所有节点,root 必须是最高级节点名称，当换成任意子节点都是取不到值的
 --*/
 --取 hobo 的所有子节点,不管 hobo 在文档中的位置。
 SELECT @x.query('//hobo')
 ----扩展：取rogue下 所有 name
 SELECT @x.query('//rogue//name')
 --取属性为id 的所有节点
 SELECT @x.query('//hobo[@id]')
 /*注释：
 XQuery不支持直接顶级 attribute 节点，必须附带上对节点的查找
 属性必须要加[]
 */
 --选取属于 root 子元素的第一个 rogue 元素。
 SELECT @x.query('/root/rogue[1]')
 --选取属于 root 子元素的最后一个 rogue 元素。
 SELECT @x.query('/root/rogue[last()]')
 --选取属于 root 子元素的倒数第二个 rogue 元素。
 SELECT @x.query('/root/rogue[last()-1]')
 --选取最前面的两个属于 root 元素的子元素的 rogue 元素。
 SELECT @x.query('/root/rogue[position()<3]')
 --选取 root 元素的所有 hobo 元素，且其中的属性 id 的值须大于 1。
 SELECT @x.query('/root//hobo[@id>1]')
 ----扩展： root 元素的所有 hobo 元素，且其中的属性 id 的值须大于 1 并且子节点 name 的值为 光辉 的。
 SELECT @x.query('/root/rogue[./hobo[@id>1 and name="光辉"]]')
 --选取 root 子元素的所有 rogue 元素，且 属性id 的值须大于 为001 子元素hobo 属性 id 的值为 1的
 SELECT @x.query('/root/rogue[@id="001" and ./hobo[@id=1]]')
 --if then else 表达式 
 SELECT @x.query('
 if ( 1=2 ) then
 /root/rogue[@id="001"]
 else
 /root/rogue[@id="002"]
 ')
 
 --路径表达式步骤中的谓词 
 SELECT @x.query('/root/rogue[1]/hobo/name')--选择第一个 /root/rogue 节点下的所有 <Name> 元素。
 SELECT @x.query('/root/rogue/hobo[1]/name')--选择 /root/rogue/hobo 节点下的所有 <Name> 元素。
 SELECT @x.query('/root/rogue/hobo/name[1]')--选择 /root/rogue/hobo 节点下的所有第一个 <Name> 元素。
 SELECT @x.query('(/root/rogue/hobo/name)[1]')--选择 /root/rogue/hobo 节点下的第一个 <Name> 元素。
 --使用聚合函数
 SELECT @x.query('count(/root/rogue/hobo/name)'),@x.query('count(/root/rogue/hobo/name[1])')
 --FLWOR 迭代语法。FLWOR 是 for、let、where、order by 和 return 的缩写词。
 --1
 SELECT @x.query('
 <result>
 { for $i in /root/rogue/hobo/name[1]
 return string($i)
 }
 </result>')
 --2
 SELECT @x.query('
 for $Loc in /root/rogue/hobo,
 $FirstStep in $Loc/name[1]
 return 
 string($FirstStep)
 ')
 --3
 SELECT @x.query('
 for $i in /root/rogue/hobo
 order by $i/@id descending
 return string($i/name[1])
 ')
 --4
 SELECT @x.query('
 for $i in /root/rogue/hobo
 order by local-name($i) 
 return string($i/name[1])
 ')
 
 
 
 
 
 /*
 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<c>c</c>';
 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>ab</r>' UNION ALL 
 SELECT 2,'<r>b</r>' UNION ALL 
 SELECT 3,'<r>d</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('' +REPLACE(CustomerAddress, ' ', '') + '' 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)
 
 
 
 /*------------------------------------------------------------------------------------------------------------------
 
 本章讲述 modify() 方法 , modify() 方法是使用 XML DML 语句在 XML 数据中插入、更新或删除节点。
 语法：modify (XML_DML) XML_DML 是 XML 数据操作语言 (DML) 中的字符串。
 XML DML 将下列区分大小写的关键字添加到 XQuery 中：
 insert --添加
 delete --删除
 replace value of --更新
 ------------------------------------------------------------------------------------------------------------------*/
 
 DECLARE @x XML 
 SET @x='
 <root>
 <rogue id="001">
 <hobo id="1">
 <name>彪</name>
 <type>流氓</type>
 </hobo>
 </rogue>
 </root>'
 
 ------------------------------------------------------插入------------------------------------------------------------
 --在 hobo 节点下插入 一个新节点
 SET @x.modify('
 insert <nickname>阿彪</nickname>
 as first
 into (/root/rogue/hobo)[1]
 ');
 SELECT @x
 --注释：如果某节点下面有多个节点的时候可以使用 as first 或 as last 来指定所需的新节点添加位置。
 
 ---在指定的 hobo 节点下，插入同一级节点
 SET @x.modify('
 insert <id>1</id>
 before (/root/rogue/hobo)[1]
 ');
 SELECT @x
 --注释：是用 before 或者 after 关键字代替 into 在指定节点的 前面 或者 后面 插入同级节点
 --after 关键字 和 before 关键字不能用于插入属性
 
 --插入属性 一次插入多个属性值/使用变量/属性定位
 DECLARE @a INT =5
 SET @x.modify(' 
 insert (
 attribute a {sql:variable("@a")},
 attribute b {".5"}
 
 ) 
 into (/root/rogue/hobo[@id=1])[1]
 '); 
 SELECT @x; 
 GO
 ----------------------------------------------------删除------------------------------------------------------------
 DECLARE @x XML 
 SET @x='
 <?Instructions for=TheWC.exe ?> 
 <root>
 <rogue id="001">
 <hobo id="1" weight="80" age="25">
 <name>彪</name>
 <type>流氓</type>
 <nickname>阿彪</nickname>
 <notes />
 </hobo>
 <page_hotspots />
 </rogue>
 <notes />
 </root>'
 -- 删除属性
 SET @x.modify('
 delete /root/rogue/hobo/@id
 ')
 SELECT @x
 
 -- 删除节点
 SET @x.modify('
 delete /root/rogue/hobo/name[1]
 ')
 SELECT @x
 
 -- 删除节点内容
 SET @x.modify('
 delete /root/rogue/hobo/type/text()
 ')
 SELECT @x
 
 -- 删除所有处理指令
 SET @x.modify('
 delete //processing-instruction()
 ')
 SELECT @x
 
 -- 删除所有的内容为空的节点
 SET @x.modify('
 delete //*[empty(./*)]
 ')
 
 SELECT @x
 
 ----------------------------------------------------修改------------------------------------------------------------
 
 DECLARE @x XML 
 SET @x='
 <root>
 <rogue id="001">
 <hobo id="1" weight="80" age="25">
 <name>彪</name>
 <type>流氓</type>
 <nickname>阿彪</nickname>
 </hobo>
 </rogue>
 </root>'
 -- 在修改语法当中 每次只能修改一个单个节点，不能批量修改或者一次修改多个值，这一点是比较郁闷的
 
 -- 修改节点值
 SET @x.modify('
 replace value of (/root/rogue/hobo/name/text())[1]
 with "光辉"
 ')
 SELECT @x
 
 -- 修改属性值
 SET @x.modify('
 replace value of (/root/rogue/hobo/@weight)[1]
 with "70"
 ')
 SELECT @x
 
 -- 使用 if 表达式
 SET @x.modify('
 replace value of (/root/rogue/hobo/@age)[1]
 with (
 if (count(/root/rogue/hobo/*) > 2) then
 "30"
 else
 "10"
 )
 ')
 SELECT @x
 
 ------------------------------------------------------一些示例------------------------------------------------------------
 
 -- 1
 DECLARE @x1 XML
 SELECT @x1 = '
 <rogue>
 <hobo name="彪"/>
 <hobo name="光辉"/>
 <hobo name="小D"/>
 <hobo name="野子"/>
 </rogue>'
 
 ------------------------------------------------------------
 -- 把 小D 移动到 彪 前面
 ------------------------------------------------------------
 SET @x1.modify('
 insert /rogue/hobo[@name="小D"] 
 before (/rogue/hobo[@name="彪"])[1]
 ')
 
 SET @x1.modify ('
 delete (/rogue/hobo[@name="小D"])[2]
 ')
 
 SELECT @x1
 
 ---------- --------------------------------------------------
 -- 把 光辉 移动到 野子 后面
 ------------------------------------------------------------
 SET @x1.modify('
 insert /rogue/hobo[@name="光辉"] 
 after (/rogue/hobo[@name="野子"])[1]
 ')
 
 SET @x1.modify ('
 delete (/rogue/hobo[@name="光辉"])[1]
 ')
 
 SELECT @x1
 
 ------------------------------------------------------------
 -- 把 野子 向前移动一级
 ------------------------------------------------------------
 SET @x1.modify('
 insert /rogue/hobo[@name="野子"] 
 before (/rogue/hobo[. << (/rogue/hobo[@name="野子"])[1]])[last()]
 ')
 
 SET @x1.modify ('
 delete /rogue/hobo[@name="野子"] 
 [. is (/rogue/hobo[@name="野子"])[last()]]
 ')
 
 SELECT @x1
 ------------------------------------------------------------
 -- 把 彪 向后 移一级
 ------------------------------------------------------------
 set @x1.modify('
 insert /rogue/hobo[@name="彪"] 
 before (/rogue/hobo[. >> (/rogue/hobo[@name="彪"])[1]])[2]
 ')
 SELECT @x1
 SET @x1.modify ('
 delete (/rogue/hobo[@name="彪"])[1]
 ')
 
 SELECT @x1
 
 ------------------------------------------------------------
 -- 使用变量修改属性名称
 ------------------------------------------------------------
 DECLARE @x2 XML
 SELECT @x2 = '
 <Employees>
 <Employee FirstName="Jacob" MiddleName="V" LastName="Sebastian"/>
 </Employees>'
 
 DECLARE @var VARCHAR(20)
 DECLARE @val VARCHAR(20)
 
 SELECT @var = 'MiddleName'
 SELECT @val = 'J'
 
 
 SET @x2.modify('
 replace value of (
 /Employees/Employee/@*[local-name()=sql:variable("@var")]
 )[1]
 with sql:variable("@val")
 ')
 
 select @x2