本文主要是介绍SQL Server2008 XML增删改查，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
create xml schema collection dbo.class_xsd as
'<?xml version="1.0" encoding="UTF-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="class">
<xs:complexType>
<xs:attribute name ="name" type="xs:string" use="required"/>
</xs:complexType>
</xs:element >
<xs:element name="classes">
<xs:complexType>
<xs:sequence>
<xs:element ref="class" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element >
</xs:schema>
'
create table tblspeakers
(
speaker_id int primary key identity,
speaker_nm nvarchar(50),
speaker_country nvarchar(25),
speaker_xml xml (class_xsd) not null
)
--xml index:primary key
create primary xml index idx_1 on tblspeakers(speaker_xml)
--xml index :path
create xml index idx_a on tblspeakers(speaker_xml) using xml index idx_1 for path
--如果没有xsd架构的话 xquery引擎会假定所有内容为非类型作为字符串处理。
--插入数据
insert into tblspeakers values('Stephen Forte','USA','
<classes>
<class name="Writing Secure Code for ASP .NET"/>
<class name="Using XQuery to Query and Manipulate XML Data in SQL Server 2008"/>
<class name="SQL Server and Oracle Working Together"/>
<class name="Protecting against SQL Injection Attacks"/>
</classes>
')
insert into tblspeakers values('Richard Campbell','Canada','
<classes>
<class name="SQL Server Profiler"/>
<class name="Advanced SQL Querying Techniques"/>
<class name="SQL Server and Oracle Working Together"/>
<class name="T-SQL Error Handling in Yukon"/>
</classes>
')
insert into tblspeakers values('John Huckaby ','USA','
<classes>
<class name="Smart Client Stuff"/>
<class name="More Smart Client Stuff"/>
</classes>
')
insert into tblspeakers values('Malek Kemmou ','Morocco','
<classes>
<class name="SmartPhone 2005"/>
<class name="Office System 2003"/>
</classes>
')
insert into tblspeakers values('Goksin Bakir ','Turkey','
<classes>
<class name="SmartPhone 2007"/>
<class name="Office System 2007"/>
</classes>
')
insert into tblspeakers values('Jan Vasters ','Germany','
<classes>
<class name="SOA"/>
<class name="Biz Talk Services"/>
</classes>
')
insert into tblspeakers values('Monica L.Tripp','USA','
<classes>
<class name="SQL Server Index "/>
<class name="SQL Precon "/>
</classes>
')
insert into tblspeakers values('Bad Speaker','France','
<classes>
<class name="SQL Server Index "/>
<class name="SQL Precon "/>
</classes>
')
select * from tblspeakers
--xml.exist 返回1或者0 null ，返回xml数据的元素和属性
declare @xml as xml
set @xml='
<classes>
<class name="SQL Server Index "/>
<class name="SQL Precon "/>
</classes>
'
select @xml.exist('/classes')
---创建check constraint中使用的函数
create function dbo.DesOrderXMLDataExist(@xml xml)
returns bit
as
begin
return @xml.exist('/Orders')
end
go
--使用这个函数检查这个xml列必须有节点/Orders
create table OrdersXMLCheck
(
OrderDocID int primary key,
xOrders XML not null default '<Orders/>'
constraint xml_orderconstraint check(dbo.DesOrderXMLDataExist(xOrders)=1)
)
--查询
select * from tblspeakers where speaker_xml.exist('/classes/class[@name="SQL Precon "]')=1
/*
speaker_id speaker_nm speaker_country speaker_xml
----------- -------------------------------------------------- ------------------------- -------------------------------------------------------------------------------------
7 Monica L.Tripp USA <classes><class name="SQL Server Index " /><class name="SQL Precon " /></classes>
8 Bad Speaker France <classes><class name="SQL Server Index " /><class name="SQL Precon " /></classes>
(2 行受影响)
*/
--XML.VALUE 查询xml中的节点。。
select speaker_id,speaker_nm,speaker_country,
speaker_xml.value('/classes[1]/class[1]/@name','varchar(50)')'classname'
from tblspeakers
/*
speaker_id speaker_nm speaker_country classname
----------- -------------------------------------------------- ------------------------- ----------------------------------
1 Stephen Forte USA Writing Secure Code for ASP .NET
2 Richard Campbell Canada SQL Server Profiler
3 John Huckaby USA Smart Client Stuff
4 Malek Kemmou Morocco SmartPhone 2005
5 Goksin Bakir Turkey SmartPhone 2007
6 Jan Vasters Germany SOA
7 Monica L.Tripp USA SQL Server Index
8 Bad Speaker France SQL Server Index
(8 行受影响)
*/
--xml.query
--工作方式和xml.value类是，区别在于它返回一个xml数据类型的值，所以可以具有更大的灵活性。
select speaker_id,speaker_nm,speaker_country,
speaker_xml.query('/classes[1]/class[1]' )'classname'
from tblspeakers
/*
speaker_id speaker_nm speaker_country classname
----------- -------------------------------------------------- ------------------------- --------------------------------------------------
1 Stephen Forte USA <class name="Writing Secure Code for ASP .NET" />
2 Richard Campbell Canada <class name="SQL Server Profiler" />
3 John Huckaby USA <class name="Smart Client Stuff" />
4 Malek Kemmou Morocco <class name="SmartPhone 2005" />
5 Goksin Bakir Turkey <class name="SmartPhone 2007" />
6 Jan Vasters Germany <class name="SOA" />
7 Monica L.Tripp USA <class name="SQL Server Index " />
8 Bad Speaker France <class name="SQL Server Index " />
(8 行受影响)
*/
--同时在查询中 xml.query可以去掉参数全部返回
select speaker_id,speaker_nm,speaker_country,
speaker_xml.query('/classes /class ')as 'classname'
from tblspeakers
where speaker_country='Canada'
/*
等价于下面的这个for。。。
for $b in /classes/class return($b) xquery扩展
查询
*/
select speaker_id,speaker_nm,speaker_country,
speaker_xml.query('for $b in /classes /class return ($b)')as 'classname'
from tblspeakers
where speaker_xml.exist('/classes/class[@name="SQL Server Profiler"]')=1
---xml.query扩展
select speaker_id,speaker_nm,speaker_country,
speaker_xml.query('
for $b in /classes/class
where $b/@name="Advanced SQL Querying Techniques"
return (<Sessions>{$b}<Speaker id="{sql:column("Speaker_ID")}">
{sql:column("Speaker_NM")}</Speaker></Sessions>)')as 'classname'
from tblspeakers
where speaker_xml.exist('/classes/class[@name="SQL Server Profiler"]')=1
/*
speaker_id speaker_nm speaker_country classname
----------- -------------------------------------------------- ------------------------- -------------------------------------------------------------------------------------------------------------------
2 Richard Campbell Canada <Sessions><class name="Advanced SQL Querying Techniques" /><Speaker id="2">Richard Campbell</Speaker></Sessions>
(1 行受影响)
*/
--XML DML
--xml.modify(insert)
update tblspeakers set speaker_xml.modify('
insert <class name="pppp"/>
into /classes[1]') where speaker_id=8
declare @newxml xml
set @newxml='<class name="Ranking and Windowing Functions in SQL Server 2008"/>'
update tblspeakers set speaker_xml.modify('insert sql:variable("@newxml") into /classes[1]' )
--xml.modify(delete)
update tblspeakers set speaker_xml.modify(
'delete /classes[1]/class[2] '
)
where speaker_id=8
select * from tblspeakers
--xml.modify(replace)
update tblspeakers set speaker_xml.modify('
replace value of classes[1]/class[1]/@name[1]
with " fuck you !!"
') where speaker_id=8
这篇关于SQL Server2008 XML增删改查的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
