SQL SERVER XML 學習總結

SQL  SERVER  XML  學習總結

最新的專案工作要做一個資料同步的功能，這些天都在做技術準備，主要是用到了微軟的Service Broker技術，在熟悉使用該技術的同時，又用到了Sql server xml的一些知識，這兩天在家，把這個sql xml的一些知識給學習了一下，下邊是一些學習過程中的總結，希望能給大家帶來一些協助。

在學習過程中主要參考(http://blog.csdn.net/beirut/article/details/8154102).

測試用的資料

use TestDB

go

create Table TbFroXML(id uniqueidentifier primary key,name nvarchar(50),[type] nvarchar(50))

go

 

insert into TbFroXML values(NEWID(),'BBen','MG')

insert into TbFroXML values(NEWID(),'BB','MG')

insert into TbFroXML values(NEWID(),'Olive','MG')

insert into TbFroXML values(NEWID(),'今天','NMG')

insert into TbFroXML values(NEWID(),'明天','NMG')

insert into TbFroXML values(NEWID(),'未來','NMG')

insert into TbFroXML(id,name) values(newid(),'笨笨')

go

1.    For Xml Path

 

--無名稱列，針對行集合中的每一行產生一個對應的row元素，格式：<row>1</row>

select 1 for xml path

 

--生產格式<row><name> XXX</name></row>

select name from TbFroXML for xml path

 

--去掉name標籤，只剩下row標籤，格式：<row>BBen</row><row>BB></row><row>Olive</row>....

select name+'' from TbFroXML for xml path

 

--只產生要查詢的列的資料，去掉row元素，格式：BB未來明天今天BBenOlive

select name+'' from TbFroXML for xml path('')

 

--產生格式：<row id='XXXXXXX'><name>BBen</name></row>

select id as '@id',name from TbFroXML for xml path

 

--以NMG標記作為行標記，格式：<NMG><name>BBen</name></NMG><NMG><name>未來</name></NMG>

select name from TbFroXML for xml path('NMG')

 

--以NMG標記作為行標記，以Mg標記作為內標記，格式：<NMG><MG>BB</MG><NMG><NMG><MG>未來</MG></NMG>

select name as MG from TbFroXML  for xml path('NMG')

 

--產生格式：<row><BBen><Olive>BB</Olive></BBen></row>

select name as 'BBen/Olive' from TbFroXML for xml path

 

--產生格式：<row id='*****'>BB></row><row id='*******'>未來</row>

select id as '@id',name as '*' from TbFroXML for xml path

 

--被指定為列名的路徑為data()，則在產生的XML 中，該值將被作為一個原子值來處理,

--產生格式：<row id='*****' name="BB"><name>BB</name>MG</row>

select id as '@id',name as '@name',name,[TYPE] as 'data()' from TbFroXML for xml path

--預設情況下，列中的Null 值映射為“缺少相應的屬性、節點或元素，使用ELEMENTS 指令請求以元素為中心的XML 並指定XSINIL 來請求為NULL 值添加元素，格式：<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="7B890E0B-C470-4E93-89A4-3041B70E8DF3"><xx>    <null xsi:nil="true" /><name>笨笨</name><type xsi:nil="true" /></xx></row>

select id as '@id',null as 'xx/null',name as 'xx/name',[TYPE] as 'xx/type' from TbFroXML for xml path ,elements xsinil

 

--ROOT('oo'),--指定向產生的XML 中添加單個頂級元素

select id as '@id' ,name from TbFroXML for xml path,root('oo')

 

2.    For Xml Row/Auto模式

 

--auto模式，格式：<TbFroXML id="41D6A175-C079-4861-9C75-2EE48A62C3BC" name="BB" type="MG" />

select * from TbFroXML for  xml auto

 

--elements選項,將每列值都映射為〈row>元素的子項目，格式：<TbFroXML><id>41D6A175-C079-4861-9C75-2EE48A62C3BC</id><name>BB</name><type>MG</type></TbFroXML>

select * from TbFroXML for xml auto,elements

 

--row 模式：格式：<row><id>7B890E0B-C470-4E93-89A4-3041B70E8DF3</id><name>笨笨</name><type xsi:nil="true" /></row>

select * from TbFroXML for xml raw,elements

 

--elements選項，值為XSINIL 時，將結果集中的值為null 的列映射為xsi：nil=true屬性的元素,格式：<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><id>41D6A175-C079-4861-9C75-2EE48A62C3BC</id><name>BB</name><type>MG</type></row>

select * from TbFroXML for xml raw, elements xsinil

 

--xmldata:結果請求架構

select * from TbFroXML for xml raw,xmldata

 

--xmlschema：結果請求架構

select * from TbFroXML for xml raw,xmlschema

3.    For XML Explicit

select 1 as Tag,

       NULL as Parent,

       3 as [node!1]  --[node!1]代表的是根節點，node為節點名，代表節點階層的節點層級

       for xml explicit

 

select 1 as Tag,

       null as Parent,

       Null as [root!1],

       null as [node!2!id!element]--[node!2!id!ELEMENT]node 代表的是節點名稱;2代表的節點層次;id 表示的是元素名稱;.ELEMENT 選項表示向<node> 元素添加了<id> 元素子級，而不是添加屬性。

union all

select 2 as Tag,

       1 as Parent,

       null,

       id

from TbFroXML where id!=null for xml explicit

4.    XPath

XPath 使用路徑運算式來選取 XML 文檔中的節點或節點集。節點是通過沿著路徑 (path) 或者步 (steps) 來選取的。

4.1 選取節點

下面列出了最有用的路徑運算式：

運算式	描述
nodename	選取此節點的所有子節點。
/	從根節點選取。
//	從匹配選擇的當前節點選擇文檔中的節點，而不考慮它們的位置。
.	選取當前節點。
..	選取當前節點的父節點。
@	選取屬性。

4.2 謂語（Predicates）

謂語用來尋找某個特定的節點或者包含某個指定的值的節點。

謂語被嵌在方括弧中。

例： /root/Info/user[1]

4.3 選取未知節點

XPath 萬用字元可用來選取未知的 XML 元素。

萬用字元	描述
*	匹配任何元素節點。
@*	匹配任何屬性節點。
node()	匹配任何類型的節點。

例：/root/info/*     info節點下的所有元素節點

/root/info/@*   info 節點下所有的屬性節點

//user[@*]      所有帶有屬性的user節點

4.4 選取若干路徑

通過在路徑運算式中使用“|”運算子，您可以選取若干個路徑。

例：//User | /root/Info/@id

5.    XQuery5.1 基本的查詢

DECLARE @dataSource XML  

SET @dataSource=' 

<root> 

  <info id="1">

    <user uid="001">

       <name>笨笨</name>

       <type>MG</type>

    </user>

  </info>

  <info id="2">

    <user uid="002">

      <name>Olive</name>

      <type>MG</type>

    </user>

  </info>

  <info id="3">

    <user uid="003">

      <name>墨遙</name>

      <type>NMG</type>

    </user>

  </info>

</root>'

--擷取root所有子節點

select @dataSource.query('.')

select @dataSource.query('root')

select @dataSource.query('/root')

 

----擷取所有的info節點

select @dataSource.query('//info')

 

----擷取所有info節點下user節點的name節點

select @dataSource.query('//info/user/name')

 

----擷取所有uid>1的所有Info節點

select @dataSource.query('/root/info[./user[@uid>1]]')

if then else 運算式

select @dataSource.query('if (1=3) then /root/info[./user[uid=1]] else /root/info[./user[name="Olive"]]')

 

--路徑運算式步驟中的謂詞,擷取第一個info節點下的user節點下的name

select @dataSource.query('/root/info[1]/user/name')

 

--彙總函式

select @dataSource.query('count(/root/info[./user[type="MG"]])')

 

--FLWOR文法：For、Let、Where、Order by、Return

select @dataSource.query('<result>{for $li in /root/info/user/name[1] return string($li)}</result>')

 

select @dataSource.query('<result>{for $li in /root/info,$lii in $li/user/name[1] return string($lii)}</result>')

 

select @dataSource.query('<result>{for $li in /root/info/user order by $li/@uid descending return $li}</result>')

 

select @dataSource.query('<result>{for $li in /root/info/user order by local-name($li) return $li}</result>')

5.2 Value()

--value()擷取某一節點或其屬性的值，然後將其賦值給nvarchar類型

select @dataSource.value('(/root/info/user[1]/name)[1]','nvarchar(20)')

5.3 Exist()

select @dataSource.exist('/root/info/user/name[text()[1]="笨笨"]')

 

--將日期類型的節點屬性轉換為日期類型再與對應的值進行比較

declare @date xml

set @date='<root date="2013-10-07"/>'

--exist()

select @date.exist('/root[(@date cast as xs:date?) eq xs:date("2013-10-07")]')

 

--將日期類型的節點的值轉換為日期類型再與對應的值進行比較，date[text()[1] cast xs:date?，將節點值轉換為日期類型

declare @date1 xml

set @date1='<root><date>2013-10-07</date></root>'

select @date1.exist('/root/date[(text()[1] cast as xs:date?) eq xs:date("2013-10-07")]')

 

--取任意屬性的值：sql:variable("@attriname")

declare @attr nvarchar(20)='uid'

if @dataSource.exist('/root/info/user/@*[local-name()=sql:variable("@attr")]')>=1

select 'OK'

else select @dataSource.query('/root/info/user/name[local-name()="笨笨"]')

 

 

5.4 Nodes()

--nodes()方法，將一個xquery運算式拆分成多行

select T.c.query('.') as result  from @dataSource.nodes('/root/info') as T(c)

select T.c.query('.') as result  from @dataSource.nodes('/root/info/user') as T(c)

select T.c.value('(@uid)[1]','varchar(10)') as id,

      T.c.value('(./name)[1]','nvarchar(20)') as name,

      T.c.value('(./type)[1]','nvarchar(20)') as [type]

      from @dataSource.nodes('/root/info/user') T(c)

--第一個value方法擷取b節點下的值:cec,第二個value方法擷取b節點下的值：c    

 

declare @xml xml='<root><a><b>c<d>e</d>c</b></a></root>'

select @xml.value('(/root/a/b)[1]','nvarchar(20)'),@xml.value('(/root/a/b/text())[1]','nvarchar(20)')

 

--組合使用

declare @xml xml ='<root><info id="001" name="Olive" type="MG"/></root>'

declare @pos int=2

select @xml.value('local-name((/root/info/@*[position()=sql:variable("@pos")])[1])','nvarchar(20)')

 

--sql:column:將普通資料列和Xml資料合併

declare @tb table (id int,data xml)

insert into @tb(id,data) select 1,'<root><info><name>Benben</name><type>MG</type></info></root>'

select id,data=data.query('<root><info><id>{sql:column("id")}</id>{/root/info/name}{/root/info/type}</info></root>') from @tb

 

---contains：模糊查詢contains(.,'XX')

select t.c.query('.') from @dataSource.nodes('/root/info/user[./name[contains(.,"笨")]]') t(c)

5.5 Modify()5.5.1 Insert

--在某一節點下添加一個子節點insert into，as first/as last 指定節點插入的位置

set @dataSource.modify('insert <sex>F</sex>  as last into (/root/info/user)[1]')

select @dataSource

 

--添加某一節點的同級節點，before/after 添加同級節點

set @dataSource.modify('insert <UID>A1</UID> before (/root/info/user/name)[1]')

select @dataSource

 

--插入屬性

declare @a int=111

set @dataSource.modify('insert (attribute a {sql:variable("@a")},

                               attribute b {".3"})

                        into (/root/info/user[@uid=001])[1]')                      

select @dataSource 

 

5.5.2 Delete

--刪除屬性

set @dataSource.modify('delete /root/info/user/@uid')

select @dataSource

 

--刪除節點

set @dataSource.modify('delete /root/info/user/type')

select @dataSource

--刪除節點內容

set @dataSource.modify('delete /root/info/user/type/text()')

select @dataSource

 

--刪除所有屬性為空白的節點

set @dataSource.modify('delete //*[empty(./*)]')

select @dataSource

 

5.5.3 Replace

--修改節點值

set @dataSource.modify('replace value of (/root/info/user/name/text())[1] with "小笨笨"')

select @dataSource

 

--修改屬性值

set @dataSource.modify('replace value of (/root/info/user/@uid)[1] with "0001"')

select @dataSource