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 keycreate primary xml index idx_1 on tblspeakers(speaker_xml)--xml index :pathcreate 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 bitas beginreturn @xml.exist('/Orders')endgo--使用这个函数检查这个xml列必须有节点/Orderscreate 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 .NET2 Richard Campbell Canada SQL Server Profiler3 John Huckaby USA Smart Client Stuff4 Malek Kemmou Morocco SmartPhone 20055 Goksin Bakir Turkey SmartPhone 20076 Jan Vasters Germany SOA7 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=8declare @newxml xmlset @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=8select * 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