一个专门有关SQL Server的中文博客. 主要面向中国的SQL Server用户. 侧重于介绍的SQL Server 2005在XML方面的功能．也会尽力解答其他SQL相关问题.
我在讲座中用的demo.
-- XML作为变量
declare @x xml
set
@x = N'<根><元素 属性="1"/></根>'
--select @x
select
@x.query(N'/根/元素')
go
-- XML作为列
create
table testXML (
id
int primary key,
xmlcol
xml )
go
-- 从XML文件中输入XML
insert testXML select 1, *
from
Openrowset(bulk 'c:\JINGHAO\myXML.xml', single_blob) as x
go
-- 找出所有叫John的人
select
* from testXML
select
xmlcol.query('//person[@name="John"]')
from testXML
go
-- 建立XML主索引
create
primary xml index p_xml_idx
on
testXML(xmlcol)
go
-- 建立索引后,查看查询计划的变化
select
xmlcol.query('//person[@name="John"]')
from testXML
go
use adventureworks
go
-- query() 和 exist() 方法
SELECT
CatalogDescription.query(N'
declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
<Product 产品="{ /PD:ProductDescription[1]/@ProductModelID }" />
'
) as Result
FROM
Production.ProductModel
where
CatalogDescription.exist('
declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
/PD:ProductDescription/PD:Features/wm:Warranty '
) = 1
go
-- value() 和 exist() 方法
SELECT
CatalogDescription.value('
declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/PD:ProductDescription/@ProductModelID)[1] '
, 'int') as Result
FROM
Production.ProductModel
WHERE
CatalogDescription.exist('
declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
/PD:ProductDescription/PD:Features/wm:Warranty '
) = 1
go
-- 一个典型的 XQuery
select
xmlcol.query('
declare ns1 = "http://abc" ns2="xyz"
for $p1 in //ns1:person, $p2 in //ns2:person[@name = "a"
where //person[@spouse = $p/@name]
order by $p/@name descending
return
<person name="{$p/@name}" job="{$p/@job}">
<spouse name="{//person[@spouse=$p/@name]/@name}"
job="{//person[@spouse=$p/@name]/@job}"/>
</person>
'
)
from
testXML
go
-- XML_DML insert
update
testXML
set
xmlcol.modify('insert <dummy/> into /census[1]')
go
select
xmlcol from testXML
-- XML_DML delete
update
testXML
set
xmlcol.modify('delete /census/dummy')
go
-- XML_DML update value with
update
testXML
set
xmlcol.modify(N'replace value of (/census/person[@name="Bill"])[1]/@job
with "Teacher"'
)
go
-- 建立XML架构集合
create
xml schema collection demoXSD as
N
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="version1"
xmlns:ns="version1">
<element name="person" type="ns:personType" />
<complexType name="personType">
<sequence>
<element name="姓名" type="string"/>
<element name="性别" type="string"/>
<element name="年龄" type="integer"/>
</sequence>
</complexType>
</schema>'
go
-- 用被类型化的XML列(typed XML column)建表
create
table person(
id
int primary key,
person
xml(demoXSD)
)
go
-- 插入符合架构version1的XML
insert person values(1,
N
'<ns:person xmlns:ns="version1">
<姓名>张三</姓名>
<性别>男</性别>
<年龄>23</年龄>
</ns:person>'
)
go
-- XML架构进化
alter
xml schema collection demoXSD add
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="version2"
xmlns:ns="version2">
<element name="person" type="ns:personType" />
<complexType name="personType">
<sequence>
<element name="姓名" type="string"/>
<element name="性别" type="string"/>
<element name="年龄" type="integer"/>
<element name="教育" type="string"/>
</sequence>
</complexType>
</schema>'
go
-- 插入符合架构version2的XML
insert
person values(2,
N
'<ns:person xmlns:ns="version2">
<姓名>张三</姓名>
<性别>男</性别>
<年龄>23</年龄>
<教育>大学</教育>
</ns:person>'
)
go
-- 找出满足version1架构的人
select
person.query('declare namespace ns="version1"; //ns:person')
from
person
where
person.exist('declare namespace ns="version1"; //ns:person')= 1
go
-- FOR XML AUTO且将结果插入表中
declare
@x xml
set
@x = ( select * from production.product
for xml auto, type )
select
@x.query('/*[contains(@Name, "Decal")]')
insert
into tableWithXML( xmlcol ) values( @x )
go
-- FOR XML PATH 可对元素和属性进行任意命名
select top 10
ProductID
as [产品/@编号],
Name
as [产品/@名字],
Color
as [产品/@颜色],
Size
as [产品/特性/@尺寸],
ListPrice
as [产品/价格]
from
production.product
where
Color is not null
and Size is not null
for
xml path, type
go
-- nodes() 方法让你把XML还原成SQL的行和列
select
ref.value('@name', 'varchar(20)') p1,
ref
.value('../@name', 'varchar(20)') p2
from
testXML cross apply xmlcol.nodes('//person') as T(ref)
go
Anonymous comments are disabled
