SQL Server操作XML(六)XML FLOWR

• 基于Query方法
  

declare @myDoc xml
set @myDoc = '
<bookstore>
  <book category="COOKING">
    <title>Everyday</title>
    <author>Giade De</author>
    <price>30.00</price>
  </book>
  <book category="COMPUTER">
    <title>Windows 2003</title>
    <author>Mike</author>
    <price>50.00</price>
  </book>
  <book category="SOFTWARE">
    <title>VS.NET2003</title>
    <author>Mike</author>
    <price>90.00</price>
  </book>
</bookstore>
'
--按照绝对路径/bookstore/book/title查询
select @myDoc.query('/bookstore/book/title')

显示结果：

 

 

<title>Everyday</title>
<title>Windows 2003</title>
<title>VS.NET2003</title>

 

 

--按照绝对路径/bookstore/book查询价格大于30并返回所有内容

 

select @myDoc.query('/bookstore/book[price>30]') 

 

显示结果：

 

<book category="COMPUTER">
  <title>Windows 2003</title>
  <author>Mike</author>
  <price>50.00</price>
</book>
<book category="SOFTWARE">
  <title>VS.NET2003</title>
  <author>Mike</author>
  <price>90.00</price>
</book>

 （1）For

--按照绝对路径/bookstore/book查询价格大于30并返回所有Title

 

select @myDoc.query('for $x in /bookstore/book
where $x/price>30
return $x/title')

显示结果：

 

<title>Windows 2003</title>
<title>VS.NET2003</title>

 --排序

 

select @myDoc.query('for $x in /bookstore/book/title
order by $x
return $x')

显示结果：

 

 <title>Everyday</title>

<title>VS.NET2003</title>
<title>Windows 2003</title>

 

 --格式化输出结果

 

select @myDoc.query('for $x in /bookstore/book/title
return <li>{data($x)}</li>')

显示结果：

 

 

<li>Everyday</li>
<li>Windows 2003</li>
<li>VS.NET2003</li>

 

• 基于Value方法(返回单值)

 

declare @myDoc xml
set @myDoc = '
<bookstore>
  <book category="COOKING" id="1" >
    <title>Everyday</title>
    <author>Giade De</author>
    <price>30.00</price>
  </book>
  <book category="COMPUTER" id="2" >
    <title>Windows 2003</title>
    <author>Mike</author>
    <price>50.00</price>
  </book>
  <book category="SOFTWARE" id="3" >
    <title>VS.NET2003</title>
    <author>Mike</author>
    <price>90.00</price>
  </book>
</bookstore>

'

--查询绝对路径/bookstore/book/下第一条记录的id值

select @myDoc.value('(/bookstore/book/@id)[1]','int')

显示结果：1

 

• 基于Exist方法（查询是否存在）

 

--查询绝对路径/bookstore/book/title下是否存在内容为VS.NET2003的记录 

 

 

select @myDoc.exist('/bookstore/book/title="VS.NET2003"')

--查询绝对路径/bookstore/book/下是否存在id为1的记录

select @myDoc.exist('/bookstore/book[@id=1]')

 

• 基于Binding Relational列和变量(暂无)

• 使用nodes方法转换XML输出（通常为关系型格式输出）
  

declare @myDoc xml
set @myDoc = '
<bookstore>
  <book category="COOKING" id="1" >
    <title>第一个</title>
    <author>Giade De</author>
    <price>30.00</price>
  </book>
    <book category="COOKING" id="1" >
    <title>第二个</title>
    <author>Giade De</author>
    <price>30.00</price>
  </book>
  <book category="COMPUTER" id="2" >
    <title>Windows 2003</title>
    <author>Mike</author>
    <price>50.00</price>
  </book>
  <book category="SOFTWARE" id="3" >
    <title>VS.NET2003</title>
    <author>Mike</author>
    <price>90.00</price>
  </book>
</bookstore>
'
--将其变为行模式，但是附带标签
select T.c.query('.')
from @myDoc.nodes('/bookstore/book/title') as T(c)
显示结果：

(无列名)
<title>第一个</title>
<title>第二个</title>
<title>Windows 2003</title>
<title>VS.NET2003</title>

 

 

--将其变为行模式,剔除标签，并剔除空值
declare @x xml
set @x = '
<root>
  <row id="1">
  <name>Lorry</name>
  <oflw>some text</oflw>
  </row>
  <row id="2">
    <name>Joe</name>
  </row>
  <row id="3">
  </row>
</root>'
--id为Attribute，所以用@；text为Element
select T1.rows.value('@id','int') as id,
       T1.rows.query('name/text()') as name
from @x.nodes('root/row') T1(rows)
--自连接
outer apply T1.rows.nodes('./name') as T2(names)
--筛除空值
where T2.names is not null

 显示结果：

 

 id    name

1    Lorry
2    Joe

 

 

• 修改XML

1、INSERT
declare @doc xml
set @doc='<Products></Products>'

set @doc.modify(
'insert (<Product><ID>L01</ID><Name>LL01</Name></Product>)
into (/Products)[1]')

set @doc.modify(
'insert (<Product><ID>L02</ID><Name>LL02</Name></Product>)
as first into (/Products)[1]')

set @doc.modify(
'insert (<Product><ID>L03</ID><Name>LL03</Name></Product>)
as last into (/Products)[1]')

set @doc.modify(
'insert attribute Price {"20.50"} into (/Products/Product)[1]')

select @doc

显示结果：

 

<Products>
  <Product Price="20.50">
    <ID>L02</ID>
    <Name>LL02</Name>
  </Product>
  <Product>
    <ID>L01</ID>
    <Name>LL01</Name>
  </Product>
  <Product>
    <ID>L03</ID>
    <Name>LL03</Name>
  </Product>
</Products> 

 

------------------------------------------
2、DELETE
declare @myDoc xml
set @myDoc = '
<bookstore>
  <book category="COOKING" id="1" >
    <title>Everyday</title>
    <author>Giade De</author>
    <price>30.00</price>
  </book>
  <book category="COMPUTER" id="2" >
    <title>Windows 2003</title>
    <author>Mike</author>
    <price>50.00</price>
  </book>
  <book category="SOFTWARE" id="3" >
    <title>VS.NET2003</title>
    <author>Mike</author>
    <price>90.00</price>
  </book>
</bookstore>
'

 

--删除记录
--set @mydoc.modify('delete (/bookstore/book[@id="1"])')
--------------------------------------
--删除第i个满足条件的记录
set @mydoc.modify('delete (/bookstore/book[@id="1"])[2]')
select @myDoc
----------------------------------------
--删除单价标签和值
--set @mydoc.modify('delete (/bookstore/book/price)[1]')
----------------------------
--删除单价的数值，保留标签
set @mydoc.modify('delete (/bookstore/book/price/text())[1]')

-----------------------------
3、REPLACE
update text:
--将第i个记录的id值改为10

set @mydoc.modify('replace value of (/bookstore/book/@id)[2] with "10"')

--将第i个记录的price标签值值改为99.50

set @mydoc.modify('replace value of (/bookstore/book/price/text())[1] with "99.50"')
------------
set @mydoc.modify('
replace value of (/bookstore/book/@id)[1]
with(
if(/bookstore/book[@id="1"]) then
  "10"
else
  "100"
)
')