015_xml_函数
--环境准备*******************************************************************
USE test
--f:/test_xml.XML 【文件格式为UTF-8】
/*
<?xml version="1.0" encoding="gb2312"?>
<bookstore>
<book category="COOKING">
<title lang="en">Everyday Italian</title>
<author>Giada De Laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>
<book category="CHILDREN">
<title lang="jp">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="WEB">
<title lang="en">XQuery Kick Start</title>
<author>James McGovern</author>
<author>Per Bothner</author>
<author>Kurt Cagle</author>
<author>James Linn</author>
<author>Vaidyanathan Nagarajan</author>
<year>2003</year>
<price>49.99</price>
</book>
<book category="WEB">
<title lang="cn">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>
*/
--创建测试表
CREATE TABLE test_xml(id UNIQUEIDENTIFIER,tp XML)
--插入数据
INSERT INTO test_xml
SELECT NEWID()
,N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
UNION ALL
SELECT NEWID()
,* FROM OPENROWSET(BULK 'f:/test_xml.xml',SINGLE_BLOB) a
SELECT * FROM test_xml WHERE id=N'4273465F-11DA-42C9-A457-E1ABAEE0CE58' FOR XML PATH,ELEMENTS
--一.query() 方法*******************************************************************
--取 category="COOKING" 的book节点及其以下所有节点 【<book category="COOKING">】
SELECT tp.query('/bookstore/book[@category="COOKING"]') xml
FROM test_xml WHERE id=N'4273465F-11DA-42C9-A457-E1ABAEE0CE58'
--取 author="Giada De Laurentiis" 的book节点及其以下所有节点 【<author>Giada De Laurentiis</author>】
SELECT tp.query('/bookstore/book[author="Giada De Laurentiis"]') xml
FROM test_xml WHERE id=N'4273465F-11DA-42C9-A457-E1ABAEE0CE58'
--取title的所有节点，无论title在哪一级
SELECT tp.query('//title') xml FROM test_xml WHERE id=N'4273465F-11DA-42C9-A457-E1ABAEE0CE58'
--二.exist() 方法 *******************************************************************
--【 用来判断 XQuery 表达式返回的结果是否为空 】
/*
1，表示 True（如果查询中的 XQuery 表达式返回一个非空结果）。即，它至少返回一个 XML 节点。
0，表示 False（如果它返回一个空结果）。
NULL（如果执行查询的 xml 数据类型实例包含 NULL）。
*/
--查看路径下的元素或属性值是否存在
SELECT tp.exist('/bookstore/book[@category="COOKING"]')
FROM test_xml
WHERE id=N'4273465F-11DA-42C9-A457-E1ABAEE0CE58'
--三.value() 方法*******************************************************************
-- 【需要指明两个参数，一个为xquery， 另一个为得到数据的类型】【区分大小写】
--查询第三个title的值
SELECT tp.value('(/bookstore/book/title)[3]','nvarchar(max)') xml
FROM test_xml
WHERE id=N'4273465F-11DA-42C9-A457-E1ABAEE0CE58'
--Orders/@CustomerID,其中CustomerID是Orders的元素属性，而不是下一级元素
SELECT tp.value('(/ROOT/Customers/Orders/@CustomerID)[1]','nvarchar(max)') xml
FROM test_xml
WHERE id=N'A9CDA489-78A7-48C8-AF24-03648CD81F9A'
--四.notes() 方法 *******************************************************************
--【输入为XQuery表达式，返回一个XML格式文档的一列行集】
SELECT #T.c.query('.') FROM test_xml
cross apply tp.nodes('/bookstore/book[1]') as #T(c)
WHERE id=N'4273465F-11DA-42C9-A457-E1ABAEE0CE58'
SELECT #T.c.value('(./title)[1]','nvarchar(max)') FROM test_xml
cross apply tp.nodes('/bookstore/book') as #T(c)
WHERE id=N'4273465F-11DA-42C9-A457-E1ABAEE0CE58'
--五.modify() 方法*******************************************************************
--Ⅰ,插入 insert---------------------------------------------
--into 默认插入到最后
update test_xml
SET tp.modify('insert <test>liwz_01</test>
into (/bookstore/book)[1]')
WHERE tp.exist('/bookstore/book/title[@lang="en"]')=1
--as first--as last
update test_xml
SET tp.modify('insert <test>liwz_02</test>
as first
into (/bookstore/book)[1]')
--before--after
update test_xml
SET tp.modify('insert <test>liwz_04</test>
before (/bookstore/book/year)[1]')
--查询
SELECT * FROM test_xml WHERE id=N'4273465F-11DA-42C9-A457-E1ABAEE0CE58' FOR XML PATH,ELEMENTS
--Ⅱ，删除 delete---------------------------------------------
UPDATE test_xml
SET tp.modify('delete /bookstore/book[@category="COOKING"]/test[1]')
WHERE id=N'4273465F-11DA-42C9-A457-E1ABAEE0CE58'
--Ⅲ，替换 replace ---------------------------------------------
--[1]放到右括号外面
UPDATE test_xml
SET tp.modify('replace value of (/bookstore/book/title/@lang)[1]
with "test_replace" ')
WHERE id=N'4273465F-11DA-42C9-A457-E1ABAEE0CE58'
UPDATE test_xml
SET tp.modify('replace value of (/bookstore/book/test)[1]
with <test>test_replace</test> ')
WHERE id=N'4273465F-11DA-42C9-A457-E1ABAEE0CE58'
--注意：
--1.OPENROWSET(BULK 'f:/test_xml.xml',SINGLE_BLOB)
--将 【f:/test_xml.XML】 调整为UTF-8时，传输到sqlserver中才不是乱码
--2.T-SQL XQuery包含如下函数
/**
query(XPath条件)： 结果为 xml 类型; 返回由符合条件的节点组成的非类型化的 XML 实例
value(XPath条件,数据类型)：结果为指定的标量值类型; xpath条件结果必须唯一
exist(XPath条件)：结果为布尔值; 表示节点是否存在,如果执行查询的 XML 数据类型实例包含NULL则返回NULL
nodes(XPath条件): 返回由符合条件的节点组成的一行一列的结果表
*/
--3.区分大小写
OPENROWSET
OPENXML
FOR XML