SQL Server中的XML資料型別詳解

2022-05-21 13:01:06

一、建立測試資料，指定欄位資料型別為XML

1、建立表

--建立表，包含Xml型別列
　　CREATE TABLE Person
　　(
    　　Id int,
    　　Info xml
　　)

2、插入測試資料

--插入3條測試資料
　　INSERT Person VALUES(1,'<Person><ID>1</ID><Name>劉備</Name></Person>')
　　INSERT Person VALUES(2,'<Person><ID>2</ID><Name>關羽</Name></Person>')
　　INSERT Person VALUES(3,'<Person><ID>3</ID><Name>張飛</Name></Person>')

3、插入XML檔案資料

insert Person values(4,select * from  openrowset(bulk 'G:DocumentXMLDocumentx3.xml',single_clob) as x)

4、建立索引

--XML「主」索引  
create primary xml index IX_Person_Info
    on Person ( Info );

--XML「路徑」輔助索引  
create xml index IX_Person_Info_Path
    on Person ( Info )
    using xml index IX_Person_Info for path;

--XML「屬性」輔助索引  
create xml index IX_Person_Info_Property
    on Person ( Info )
    using xml index IX_Person_Info for property;

--XML「內容」輔助索引  
create xml index IX_Person_Info_value
    on Person ( Info )
    using xml index IX_Person_Info for value;

二、查詢XML資料

T-SQL 支援用於查詢 XML 資料型別的 XQuery 語言。

XQuery 基於現有的 XPath 查詢語言，並支援更好的迭代、更好的排序結果以及構造必需的 XML 的功能。

1、query(XPath條件):返回xml 型別的節點內容

--查詢節點內容query()方法
SELECT Id,Info.query('(/Person/Name)[1]') FROM Person WHERE ID = 2

複雜查詢

declare @myxml xml
set @myxml='<people>
              <student id="201301">
                  <Name>王五</Name>
                  <Age>18</Age>
                  <Address>湖南</Address>
              </student>
              <student id="201302">
                  <Name>李一</Name>
                  <Age>20</Age>
                  <Address>湖北</Address>
              </student>
            </people>'

select @myxml.query('
                for $ss in /people/student
                where $ss/Age[text()]<22
                return     element Res
                {    
                    (attribute age{data($ss/Age[text()[1]])})
                }')

結果為: <Res age="18" /><Res age="20" />

一個完整範例：

declare @x xml;

set @x = '
<root>
  <people id="001">
    <student  id="1">
      <name>彪</name>
      <name>阿彪</name>
      <type>流氓</type>
    </student >
  </people>
  <people id="002">
    <student  id="2">
      <name>光輝</name>
      <name>二輝</name>
      <type>流氓</type>
    </student >
  </people>
  <people id="001">
    <student  id="3">
      <name>小德</name>
      <name>小D</name>
      <type>臭流氓</type>
    </student >
  </people>
</root>';

--1、取root的所有子節點
select @x.query('root'), @x.query('/root'), @x.query('.');

--/*註釋：
--    這裡實際上是取所有節點,root 必須是最高階節點名稱，當換成任意子節點都是取不到值的
--*/

--2、取 student  的所有子節點,不管 student  在檔案中的位置。
select @x.query('//student ');

--3、取people下 所有  name
select @x.query('//people//name');

--4、取屬性為id 的所有節點
select @x.query('//student [@id]');

/*註釋：
    XQuery不支援直接頂級 attribute 節點，必須附帶上對節點的查詢
    屬性必須要加[]
*/
--5、選取屬於 root 子元素的第一個 people 元素。
select @x.query('/root/people[1]');

--6、選取屬於 root 子元素的最後一個 people 元素。
select @x.query('/root/people[last()]');

--7、選取屬於 root 子元素的倒數第二個 people 元素。
select @x.query('/root/people[last()-1]');

--8、選取最前面的兩個屬於 root 元素的子元素的 people 元素。
select @x.query('/root/people[position()<3]');

--9、選取 root 元素的所有 student  元素，且其中的屬性 id 的值須大於 1。
select @x.query('/root//student [@id>1]');

----10、 root 元素的所有 student  元素，且其中的屬性 id 的值須大於 1 並且子節點 name 的值為 光輝 的。
select @x.query('/root/people[./student [@id>1 and name="光輝"]]');

--11、選取 root 子元素的所有 people 元素，且 屬性id 的值須大於 為001 子元素student  屬性 id 的值為 1的
select @x.query('/root/people[@id="001" and ./student [@id=1]]');

--12、if then else 表示式  
select @x.query('
if ( 1=2 ) then
  /root/people[@id="001"]
else
  /root/people[@id="002"]
');

--13、路徑表示式步驟中的謂詞 
select @x.query('/root/people[1]/student /name'); --選擇第一個 /root/people 節點下的所有 <Name> 元素。

select @x.query('/root/people/student [1]/name'); --選擇 /root/people/student  節點下的所有 <Name> 元素。

select @x.query('/root/people/student /name[1]'); --選擇 /root/people/student  節點下的所有第一個 <Name> 元素。

select @x.query('(/root/people/student /name)[1]');

--選擇 /root/people/student  節點下的第一個 <Name> 元素。

--14、使用聚合函數
select @x.query('count(/root/people/student /name)'), @x.query('count(/root/people/student /name[1])');

--15、FLWOR 迭代語法。FLWOR 是 for、let、where、order by 和 return 的縮寫詞。
--1
select @x.query('
<result>
 { for $i in /root/people/student /name[1]
    return string($i)
 }
</result>');
--<result>彪 光輝 小德</result>

--2
select @x.query('
   for $Loc in /root/people/student ,
       $FirstStep in $Loc/name[1]
   return 
       string($FirstStep)
');
--彪 光輝 小德

--3
select @x.query('
   for $i in /root/people/student 
   order by $i/@id descending
   return string($i/name[1])
');
--小德 光輝 彪

--4
select @x.query('
   for $i in /root/people/student 
   order by local-name($i) 
   return string($i/name[1])
');
--彪 光輝 小德

2、value(XPath條件,資料型別)：返回標量值

該方法對xml執行XQuery查詢,返回SQL型別的標量值。xpath條件結果必須唯一。

SELECT Id,Info.value('(/Person/Name)[1]','VARCHAR(50)') FROM Person WHERE ID = 2
SELECT * FROM Person WHERE Info.value('(/Person/Name)[1]','VARCHAR(50)') = '張飛'

3、exist(XPath條件)：返回是否存在

結果為布林值; 表示節點是否存在,如果執行查詢的 XML 資料型別範例包含NULL則返回NULL。

SELECT * FROM Person WHERE Info.exist('(/Person/Name)[1]') = 1

一個完整範例：

--1、判斷 student 中屬性 id  的值 是否為空
select @x.exist('(/root/people/student/@id)[1]');
--2、判斷指定節點值是否相等
declare @xml xml = '<root><name>a</name></root>';
select @xml.exist('(/root/name[text()[1]="a"])');

--3、比較日期
--程式碼 cast as xs:date? 用於將值轉換為 xs:date 型別，以進行比較。
--@Somedate 屬性的值是非型別化的。比較時，此值將隱式轉換為比較右側的型別（xs:date 型別）。
--可以使用 xs:date() 建構函式，而不用 cast as xs:date()。
declare @a xml;
set @a = '<root Somedate = "2012-01-01Z"/>';
select @a.exist('/root[(@Somedate cast as xs:date?) eq xs:date("2012-01-01")]');

4、nodes(XPath條件)：返回由符合條件的節點組成的多行一列的結果表

語法: nodes(QueryString) as table(column)

如果要將xml資料型別拆分為關係資料，使用nodes方法將非常有效，它允許使用者將標識對映到新行的節點。

--查詢節點
　　SELECT T2.Loc.query('.') as result
　　FROM Person
　　CROSS APPLY Info.nodes('/Person/Name') as T2(Loc)

例二：-將 student節點拆分成多行

--獲得所有student節點的資料,每一行顯示一條student節點的資料
select T.c.query('.')  as result from @myxml.nodes('/people/student') as  T(c)
--將這些資料顯示為一個表格
select T.c.value('(@id)[1]','int') as id,
            T.c.value('(./Name)[1]','nvarchar(16)') as name,
            T.c.value('(./Age)[1]','int') as age,
            T.c.value('(./Address)[1]','nvarchar(16)') as address
            from @myxml.nodes('/people/student') as T(c)

一個完整的範例：

--1、 對錶中的 xml 資料進行解析, 節點下面有多個相同節點的 使用 cross apply 和 nodes() 方法解析
if object_id('tempdb..[#tb]') is not null
    drop table [#tb];

create table [#tb]
    (
        [id] int ,
        [name] xml
    );

insert [#tb]
       select 1, '<r><i>a</i><i>b</i></r>'
       union all
       select 2, '<r><i>b</i></r>'
       union all
       select 3, '<r><i>d</i></r>';

select id, T.c.query('.'), T.c.value('.', 'sysname') from [#tb] A cross apply A.name.nodes('/r/i') T(c);

--2、利用xml 拆分字串
declare @s varchar(100) = '1,2,3,4,5,6';

select T.c.value('.', 'int') as col
from   ( select cast('<x>' + replace(@s, ',', '</x><x>') + '</x>' as xml).query('.') as name ) as a
       cross apply a.name.nodes('/x') T(c);

--3、取任意屬性的屬性值，這裡引入了 sql:variable
declare @x1 xml;

select @x1 = '
<Employees Dept="IT">
  <Employee Number="1001" Name="Jacob"/>
  <Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>';

declare @pos int;

select @pos = 2;

select @x1.value('local-name(
        (/Employees/Employee[2]/@*[position()=sql:variable("@pos")])[1] )', 'VARCHAR(20)') as AttName;

--4、將普通資料列和 xml 資料列進行合併
--sql:column() 函數
declare @t1 table
    (
        id int ,
        data xml
    );

insert into @t1 ( id, data )
            select 1, '<root><name>二輝</name><type>流氓</type></root>'
            union all
            select 2, '<root><name>彪</name><type>流氓</type></root>';

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

--5、提取長度為5的數位
--string-length() 函數 和 number() 函數
declare @t table
    (
        CustomerID int ,
        CustomerAddress varchar(50)
    );

insert into @t ( CustomerID, CustomerAddress )
            select 1, '12 20 97TH STREET NEW GARDENS, NY  11415  APT 8P'
            union all
            select 2, '20-10 93RD STREET #8A VICTORIA NY 11106 19TH FLR'
            union all
            select 3, '290 BERKELEY STREET APT24D  NYC, NY  10038'
            union all
            select 4, '351-250  345 STREET PANAMA BEACH 11414  APT4F';

with cte
as ( select CustomerID, cast('<i>' + replace(CustomerAddress, ' ', '</i><i>') + '</i>' as xml).query('.') as CustomerAddress
     from   @t )
select CustomerID, x.i.value('.', 'VARCHAR(10)') as ZipCode
from   cte
       cross apply CustomerAddress.nodes('//i[string-length(.)=5][number()>0]') x(i);

三、modify()：修改XML修改XML欄位

使用此方法可以修改xml資料內容。
xml資料型別的modify方法只能在update語句的set字句中使用，注意如果是針對null值呼叫modify方法將返回錯誤。

1、modify（insert）增加節點

--modify(insert)增加節點
update Person set Info.modify('
    insert <Age>25</Age>
    into (/Person)[1]') 
where Id = 3;

範例：

--1、在 student 節點下插入 一個新節點
SET @x.modify('
insert <nickname>阿彪</nickname>
as first
into (/root/people/student)[1]
');
SELECT @x

--註釋：如果某節點下面有多個節點的時候可以使用 as first 或 as last 來指定所需的新節點新增位置。
 
---2、在指定的 student 節點下，插入同一級節點
SET @x.modify('
insert <id>1</id>
before (/root/people/student)[1]
');
SELECT @x
--註釋：是用 before 或者 after 關鍵字代替 into 在指定節點的 前面 或者 後面 插入同級節點
--after 關鍵字 和 before 關鍵字不能用於插入屬性
 
--3、插入屬性 一次插入多個屬性值/使用變數/屬性定位
DECLARE @a INT =5
SET @x.modify('           
insert (
            attribute a {sql:variable("@a")},
            attribute b {".5"}          
 
        )     
into   (/root/people/student[@id=1])[1]
');           
SELECT @x;           
GO

2、modify（delete ）刪除節點

xQuery知識，沒有text()就直接刪除節點

UPDATE Person
　　SET Info.modify('
        delete (/Person)[1]/Age/text()'
　　)
　　where ID = 3

範例：

-- 1、刪除屬性
SET @x.modify('
  delete /root/people/student/@id
')
SELECT @x
 
-- 2、刪除節點
SET @x.modify('
  delete /root/people/student/name[1]
')
SELECT @x
 
-- 3、刪除節點內容
SET @x.modify('
  delete /root/people/student/type/text()
')
SELECT @x
 
-- 4、刪除所有處理指令
SET @x.modify('
  delete //processing-instruction()
')
SELECT @x
 
-- 5、刪除所有的內容為空的節點
SET @x.modify('
delete //*[empty(./*)]
')
 
SELECT @x

-----------------------------------------------------------
-- 把  小D  移動到 彪 前面
------------------------------------------------------------
SET @x1.modify('
    insert /people/student[@name="小D"] 
    before (/people/student[@name="彪"])[1]
    ')
 
SET @x1.modify ('
        delete (/people/student[@name="小D"])[2]
    ')
 
SELECT @x1
 

------------------------------------------------------------
-- 把 野子 向前移動一級
------------------------------------------------------------
SET @x1.modify('
    insert /people/student[@name="野子"] 
    before (/people/student[. << (/people/student[@name="野子"])[1]])[last()]
    ')
 
SET @x1.modify ('
        delete /people/student[@name="野子"] 
        [. is (/people/student[@name="野子"])[last()]]
    ')
 
SELECT @x1
------------------------------------------------------------
-- 把 彪  向後 移一級
------------------------------------------------------------
set @x1.modify('
    insert /people/student[@name="彪"] 
    before (/people/student[. >> (/people/student[@name="彪"])[1]])[2]
    ')
SELECT @x1
SET @x1.modify ('
        delete (/people/student[@name="彪"])[1]
    ')
 
SELECT @x1

3、modify（replace value of） --更新單個節點

在修改語法當中每次只能修改一個單個節點，不能批次修改或者一次修改多個值，這一點是比較鬱悶的

declare @x xml;

set @x = '
<root>
  <people id="001">
    <student id="1"  weight="80" age="25">
      <name>彪</name>
      <nickname>阿彪</nickname>
      <type>流氓</type>
    </student>
  </people>
  <people id="002">
    <student id="2">
      <name>光輝</name>
      <nickname>二輝</nickname>
      <type>流氓</type>
    </student>
  </people>
</root>';

 
-- 修改節點值
SET @x.modify('
  replace value of (/root/people/student/name/text())[1]
  with     "光輝"
')
SELECT @x
 
-- 修改屬性值
SET @x.modify('
  replace value of (/root/people/student/@weight)[1]
  with     "70"
')
SELECT @x
 
-- 使用 if 表示式
SET @x.modify('
  replace value of (/root/people/student/@age)[1]
  with (
       if (count(/root/people/student/*) > 4) then
         "30"
       else
         "10"
      )
')
SELECT @x

四、for xml子句:表資料自動生成xml格式

通過使用for xml子句，我們可以檢索系統中表的資料並自動生成xml格式。一共有4種模式：RAW、AUTO、EXPLICIT、PATH。

for xml子句可以用在頂級查詢和子查詢中，頂級for xml子句只能出現在select語句中，子查詢中的for xml子句可以出現在insert、delete、update以及賦值語句中。

1、raw模式

raw模式是這4種模式裡最簡單的一種。將為select語句所返回的查詢結果集中的每一行轉換為帶有通用標記符“<row>”或可能提供元素名稱的xml元素。
預設情況下，行集中非null的列都將對映為<row>元素的一個屬性。這樣當使用select查詢時，會對結果集進行xml的轉換，它們將會被轉為row元素的屬性。

select teacherId, teacherName from teacher where teacherSex = '女' for xml raw;

--結果：<row teacherId="4" teacherName="謝一"/>
--      <row teacherId="5" teacherName="羅二"/>

select student.id, student.name, teacher.teacherId, teacher.teacherName
from   student
       inner join teacher on student.teacherId = teacher.teacherId
for xml raw;
--結果:   <row id="10" name="小李" teacherId="1" teacherName="王靜" />
--        <row id="11" name="小方" teacherId="2" teacherName="李四" />

如果將 ELEMENTS 指令新增到 FOR XML 子句，則每個列值都將對映到 <row> 元素的子元素。
指定 ELEMENTS 指令之後，您還可以選擇性地指定 XSINIL 選項以將結果集中的 NULL 列值對映到具有 xsi:nil="true" 屬性的元素。
您可以通過向 RAW 模式指定一個可選引數為該元素指定另一個名稱，如該查詢中所示。SELECT * FROM #tb FOR XML RAW('流氓們')
RAW 模式和 AUTO 模式都可以使用 ROOT , ELEMENTS XSINIL, TYPE 指令。

--> 測試資料：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL     DROP TABLE #tb


CREATE TABLE #tb        (         [id] INT IDENTITY PRIMARY KEY ,         [name] VARCHAR(4),         [type] VARCHAR(10)        )
INSERT  #tb         SELECT  '中' , 'OK'         UNION ALL         SELECT  '美' , 'NG'

--------------開始查詢--------------------------
 SELECT * FROM #tb FOR XML raw;--<row id="1" name="中" type="OK"/><row id="2" name="美" type="NG"/>

SELECT * FROM #tb FOR XML raw('行'),ELEMENTS;--<行><id>1</id><name>中</name><type>OK</type></行><行><id>2</id><name>美</name><type>NG</type></行>

2、auto模式：表名作為元素名、生成簡單的層次結構

auto模式也是返回xml資料，它與raw的區別在於返回的xml資料中，不是以raw作為元素節點名，而是使用表名作為元素名。這個是最明顯的區別。

除此之外，auto模式的結果集還可以形成簡單的層次關係。

select teacherId, teacherName from teacher where teacherSex = '女' for xml auto;
--結果：<teacher teacherId="4" teacherName="謝一"/>
--      <teacher teacherId="5" teacherName="羅二"/>


select student.id, student.name, teacher.teacherId, teacher.teacherName
from   student
       inner join teacher on student.teacherId = teacher.teacherId
for xml auto;
/*  生成了巢狀關係
<student id="10" name="小李              ">
  <teacher teacherId="1" teacherName="王靜" />
</student>
<student id="11" name="小方              ">
  <teacher teacherId="2" teacherName="李四" />
</student>
*/

3、path模式

--> 測試資料：#tb
if object_id('TEMPDB.DBO.#tb') is not null
    drop table #tb;

create table #tb
    (
        [id] int identity primary key ,
        [name] varchar(4) ,
        [type] varchar(10)
    );

insert #tb select '中', 'OK' union all select '美', 'NG';

--------------開始查詢--------------------------
--1、沒有名稱的列
--生成此 XML。 預設情況下，針對行集中的每一行，生成的 XML 中將生成一個相應的 <row> 元素。 這與 RAW 模式相同。
select 1 for xml path;
--<row>1</row>

--2、延伸
select [name] + '' from #tb for xml path;
--select [name] + '' from #tb for xml path;

--3、去掉<row> 元素
select [name] + '' from #tb for xml path('');
--中美

--4、具有名稱的列
select [name] from #tb for xml path;
--<row><name>中</name></row><row><name>美</name></row>

--5、列名以 @ 符號開頭。
select id as '@id', [name] from #tb for xml path;
--<row id="1"><name>中</name></row><row id="2"><name>美</name></row>

--6、列名不以 @ 符號開頭
select [name] as 臭流氓 from #tb for xml path('一群流氓');
--<一群流氓><臭流氓>中</臭流氓></一群流氓><一群流氓><臭流氓>美</臭流氓></一群流氓>

--7、列名以 @ 符號開頭幷包含斜槓標記 (/)
select id as '@id', [name] as '一群流氓/臭流氓' from #tb for xml path;
--<一群流氓><臭流氓>中</臭流氓></一群流氓><一群流氓><臭流氓>美</臭流氓></一群流氓>

--8、名稱指定為萬用字元的列
--如果指定的列名是一個萬用字元 (*)，則插入此列的內容時就像沒有指定列名那樣插入。 
--如果此列不是 xml 型別的列，則此列的內容將作為文位元組點插入
select id as '@id', [name] as '*' from #tb for xml path;
--<row id="1">中</row><row id="2">美</row>

--9、列名為 XPath 節點測試的列
--text()
--對於名為 text() 的列，該列中的字串值將被新增為文位元組點。
--comment()
--對於名為 comment() 的列，該列中的字串值將被新增為 XML 註釋。
--node()
--對於名為 node() 的列，結果與列名為萬用字元 (*) 時相同。
--處理指令（名稱）
--如果列名為處理指令，該列中的字串值將被新增為此處理指令目標名稱的 PI 值。
select id as '@id', '臭流氓' as 'text()', '一個臭流氓' as "processing-instruction(PI)", 'chouliumang' as 'comment()', [name] as 'EmpName/text()' ,
       [name] as '臭流氓/node()'
from   #tb
where  id = 1
for xml path;
--<row id="1">臭流氓<?PI 一個臭流氓?><!--chouliumang--><EmpName>中</EmpName><臭流氓>中</臭流氓></row>

--10、帶有指定為 data() 的路徑的列名
--如果被指定為列名的路徑為 data()，則在生成的 XML 中，該值將被作為一個原子值來處理。 
--如果序列化中的下一項也是一個原子值，則將向 XML 中新增一個空格字元。 
--這在建立列表型別化元素值和屬性值時很有用。 以下查詢將檢索產品型號 ID、名稱和該產品型號中的產品列表。
select id as '@id', [name] as '@name', [name], [type] as 'data()' from #tb where id = 1 for xml path;
--<row id="1" name="中"><name>中</name>OK</row>

--11、預設情況下，列中的 Null 值對映為「缺少相應的屬性、節點或元素」。 
--通過使用 ELEMENTS 指令請求以元素為中心的 XML 並指定 XSINIL 來請求為 NULL 值新增元素，
--可以覆蓋此預設行為，如以下查詢所示：
--未指定 XSINIL，將缺少 <null> 元素。
select id as '@id', null as 'xx/null', [name] as 'xx/name', [type] as 'xx/type' from #tb for xml path;
--<row id="1"><xx><name>中</name><type>OK</type></xx></row><row id="2"><xx><name>美</name><type>NG</type></xx></row>
select id as '@id', null as 'xx/null', [name] as 'xx/name', [type] as 'xx/type' from #tb for xml path, elements xsinil;
--<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="1"><xx><null xsi:nil="true"/><name>中</name><type>OK</type></xx></row><row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="2"><xx><null xsi:nil="true"/><name>美</name><type>NG</type></xx></row>

--12、ROOT/TYPE/BINARY選項
select id as '@id', [name], [type], 0x78786F6F as 'VARBINARY'
from   #tb
for xml path, root('oo'), --指定向產生的 XML 中新增單個頂級元素。 可以選擇指定要生成的根元素名稱。 預設值為「root」。
type, --指定查詢以 xml 型別返回結果。
binary base64; --如果指定 BINARY Base64 選項，則查詢所返回的任何二進位制資料都用 base64 編碼格式表示。
--若要使用 RAW 和 EXPLICIT 模式檢索二進位制資料，必須指定此選項。 
--在 AUTO 模式中，預設情況下將二進位制資料作為參照返回。 有關使用範例，請參閱將 RAW 模式與 FOR XML 一起使用。
--<oo><row id="1"><name>中</name><type>OK</type><VARBINARY>eHhvbw==</VARBINARY></row><row id="2"><name>美</name><type>NG</type><VARBINARY>eHhvbw==</VARBINARY></row></oo>

到此這篇關於SQL Server操作XML型別的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。