xml及SQL作為兩種存儲數據的主流方式,各自有各自的優勢.為此主流的數據庫產品中陸續的加入了對XML的支持.

1.對于SQL返回的結果集可以對其進行XML轉換.

2.在MS SQL2005中加入了XML類型.允許將XML數據存入對應的表列中,并且也實現了結合XQuery(部分功能未實現)來對XML數據進行操作的功能.

創建對應的數據庫及表信息.

create database people;use people;create table people (id int identity PRimary key,firstname text not null,lastname text not null,age int not null,sex varchar(10) not null)初始化數據.
insert into people values ('Ricky','Lin',30,'male');insert into people values ('Jennie','Lin',30,'female');查詢數據并以XML格式返回.
select id [@id],firstname [name/firstname],lastname [name/lastname],age [age],sex [sex] from people for xml path('person'),root('people')獲取到的結果.
<people>  <person id="1">    <name>      <firstname>Ricky</firstname>      <lastname>Lin</lastname>    </name>    <age>30</age>    <sex>male</sex>  </person>  <person id="2">    <name>      <firstname>Jennie</firstname>      <lastname>Lin</lastname>    </name>    <age>30</age>    <sex>female</sex>  </person></people>另外還有for xml auto / for xml raw 等選項也可以支持XML輸出.并可以添加對應的參數來豐富輸出的格式.
2.SQL對于XML的支持更重要的表現在XML類型的存儲和操作.以下主要說明的是modify/query這兩個函數.其中主要的應用是針對XQuery.
declare @original xml;set @original = '<person>	<firstname>Ricky</firstname>	<lastname>Lin</lastname></person>';/*modify*/declare @delete xml;declare @insert xml;declare @replace xml;set @delete = @original;set @insert = @original;set @replace = @original;set @delete.modify('delete /person/*[1]') ;select @delete 'delete';/*delete firstname element.*/set @insert.modify('insert <middle>OK</middle> after (/person/firstname)[1]');select @insert 'insert';/*insert the middle name after firstname*/declare @replacement varchar(20);set @replacement = 'Jennie';set @replace.modify('replace value of (/person/firstname/text())[1] with sql:variable("@replacement")');select @replace 'replace';/*replace the firstname 'Ricky' to 'Jennie'*/
modify函數主要提供了三種方式對XML類型數據的修改.
delete/replace/insert. 其參數中主要應用了XQuery/XPath來獲取對應的節點以供操作.
以下例子是使用query函數來處理帶名稱空間的的情況.
declare @data xml;set @data = '<person xmlns="http://www.ricky.com">	<firstname>Ricky</firstname>	<lastname>Lin</lastname></person>';select @data.query('	declare namespace ricky = "http://www.ricky.com";	<name>{ricky:person/ricky:firstname/text()}-{ricky:person/ricky:lastname/text()}</name>') result;在SQL與XML結合使用的過程中,主要的應用實現行是針對于XQuery.當然或許現階段SQL并無法完全的兼容XQuery語法.但是已經足夠了日常需求.