SQL?Server中的XML數(shù)據(jù)類型詳解
更新時(shí)間：2022年05月21日 10:13:43 作者：springsnow
本文詳細(xì)講解了SQL?Server中的XML數(shù)據(jù)類型，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
SQL Server從2005起開始支持xml類型，這個(gè)數(shù)據(jù)類型對(duì)于后期的改變非常有用。一對(duì)多的關(guān)系在后期變成了多對(duì)多的關(guān)系，XML類型就是一個(gè)不錯(cuò)的選擇。
一、創(chuàng)建測(cè)試數(shù)據(jù)，指定字段數(shù)據(jù)類型為XML
1、創(chuàng)建表
--創(chuàng)建表，包含Xml類型列
CREATE TABLE Person
(
Id int,
Info xml
)2、插入測(cè)試數(shù)據(jù)
--插入3條測(cè)試數(shù)據(jù) INSERT Person VALUES(1,'<Person><ID>1</ID><Name>劉備</Name></Person>') INSERT Person VALUES(2,'<Person><ID>2</ID><Name>關(guān)羽</Name></Person>') INSERT Person VALUES(3,'<Person><ID>3</ID><Name>張飛</Name></Person>')
3、插入XML文件數(shù)據(jù)
insert Person values(4,select * from openrowset(bulk 'G:\Document\XMLDocument\x3.xml',single_clob) as x)
4、創(chuàng)建索引
--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“內(nèi)容”輔助索引
create xml index IX_Person_Info_value
on Person ( Info )
using xml index IX_Person_Info for value;二、查詢XML數(shù)據(jù)
T-SQL 支持用于查詢 XML 數(shù)據(jù)類型的 XQuery 語言。
XQuery 基于現(xiàn)有的 XPath 查詢語言，并支持更好的迭代、更好的排序結(jié)果以及構(gòu)造必需的 XML 的功能。
1、query(XPath條件):返回xml 類型的節(jié)點(diǎn)內(nèi)容
--查詢節(jié)點(diǎn)內(nèi)容query()方法
SELECT Id,Info.query('(/Person/Name)[1]') FROM Person WHERE ID = 2
復(fù)雜查詢
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]])})
}')結(jié)果為: <Res age="18" /><Res age="20" />
一個(gè)完整實(shí)例：
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的所有子節(jié)點(diǎn)
select @x.query('root'), @x.query('/root'), @x.query('.');
--/*注釋：
-- 這里實(shí)際上是取所有節(jié)點(diǎn),root 必須是最高級(jí)節(jié)點(diǎn)名稱，當(dāng)換成任意子節(jié)點(diǎn)都是取不到值的
--*/
--2、取 student 的所有子節(jié)點(diǎn),不管 student 在文檔中的位置。
select @x.query('//student ');
--3、取people下 所有 name
select @x.query('//people//name');
--4、取屬性為id 的所有節(jié)點(diǎn)
select @x.query('//student [@id]');
/*注釋：
XQuery不支持直接頂級(jí) attribute 節(jié)點(diǎn)，必須附帶上對(duì)節(jié)點(diǎn)的查找
屬性必須要加[]
*/
--5、選取屬于 root 子元素的第一個(gè) people 元素。
select @x.query('/root/people[1]');
--6、選取屬于 root 子元素的最后一個(gè) people 元素。
select @x.query('/root/people[last()]');
--7、選取屬于 root 子元素的倒數(shù)第二個(gè) people 元素。
select @x.query('/root/people[last()-1]');
--8、選取最前面的兩個(gè)屬于 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 并且子節(jié)點(diǎn) 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 表達(dá)式
select @x.query('
if ( 1=2 ) then
/root/people[@id="001"]
else
/root/people[@id="002"]
');
--13、路徑表達(dá)式步驟中的謂詞
select @x.query('/root/people[1]/student /name'); --選擇第一個(gè) /root/people 節(jié)點(diǎn)下的所有 <Name> 元素。
select @x.query('/root/people/student [1]/name'); --選擇 /root/people/student 節(jié)點(diǎn)下的所有 <Name> 元素。
select @x.query('/root/people/student /name[1]'); --選擇 /root/people/student 節(jié)點(diǎn)下的所有第一個(gè) <Name> 元素。
select @x.query('(/root/people/student /name)[1]');
--選擇 /root/people/student 節(jié)點(diǎn)下的第一個(gè) <Name> 元素。
--14、使用聚合函數(shù)
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條件,數(shù)據(jù)類型)：返回標(biāo)量值
該方法對(duì)xml執(zhí)行XQuery查詢,返回SQL類型的標(biāo)量值。xpath條件結(jié)果必須唯一。
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條件)：返回是否存在
結(jié)果為布爾值; 表示節(jié)點(diǎn)是否存在,如果執(zhí)行查詢的 XML 數(shù)據(jù)類型實(shí)例包含NULL則返回NULL。
SELECT * FROM Person WHERE Info.exist('(/Person/Name)[1]') = 1一個(gè)完整實(shí)例：
--1、判斷 student 中屬性 id 的值 是否為空
select @x.exist('(/root/people/student/@id)[1]');
--2、判斷指定節(jié)點(diǎn)值是否相等
declare @xml xml = '<root><name>a</name></root>';
select @xml.exist('(/root/name[text()[1]="a"])');
--3、比較日期
--代碼 cast as xs:date? 用于將值轉(zhuǎn)換為 xs:date 類型，以進(jìn)行比較。
--@Somedate 屬性的值是非類型化的。比較時(shí)，此值將隱式轉(zhuǎn)換為比較右側(cè)的類型（xs:date 類型）。
--可以使用 xs:date() 構(gòu)造函數(shù)，而不用 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條件)：返回由符合條件的節(jié)點(diǎn)組成的多行一列的結(jié)果表
語法: nodes(QueryString) as table(column)
如果要將xml數(shù)據(jù)類型拆分為關(guān)系數(shù)據(jù)，使用nodes方法將非常有效，它允許用戶將標(biāo)識(shí)映射到新行的節(jié)點(diǎn)。
--查詢節(jié)點(diǎn)
SELECT T2.Loc.query('.') as result
FROM Person
CROSS APPLY Info.nodes('/Person/Name') as T2(Loc)
例二：-將 student節(jié)點(diǎn)拆分成多行
--獲得所有student節(jié)點(diǎn)的數(shù)據(jù),每一行顯示一條student節(jié)點(diǎn)的數(shù)據(jù)
select T.c.query('.') as result from @myxml.nodes('/people/student') as T(c)
--將這些數(shù)據(jù)顯示為一個(gè)表格
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)
一個(gè)完整的實(shí)例：
--1、 對(duì)表中的 xml 數(shù)據(jù)進(jìn)行解析, 節(jié)點(diǎn)下面有多個(gè)相同節(jié)點(diǎn)的 使用 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、將普通數(shù)據(jù)列和 xml 數(shù)據(jù)列進(jìn)行合并
--sql:column() 函數(shù)
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、提取長(zhǎng)度為5的數(shù)字
--string-length() 函數(shù) 和 number() 函數(shù)
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數(shù)據(jù)內(nèi)容。
xml數(shù)據(jù)類型的modify方法只能在update語句的set字句中使用，注意如果是針對(duì)null值調(diào)用modify方法將返回錯(cuò)誤。
1、modify（insert）增加節(jié)點(diǎn)
--modify(insert)增加節(jié)點(diǎn)
update Person set Info.modify('
insert <Age>25</Age>
into (/Person)[1]')
where Id = 3;
實(shí)例：
--1、在 student 節(jié)點(diǎn)下插入 一個(gè)新節(jié)點(diǎn)
SET @x.modify('
insert <nickname>阿彪</nickname>
as first
into (/root/people/student)[1]
');
SELECT @x
--注釋：如果某節(jié)點(diǎn)下面有多個(gè)節(jié)點(diǎn)的時(shí)候可以使用 as first 或 as last 來指定所需的新節(jié)點(diǎn)添加位置。
---2、在指定的 student 節(jié)點(diǎn)下，插入同一級(jí)節(jié)點(diǎn)
SET @x.modify('
insert <id>1</id>
before (/root/people/student)[1]
');
SELECT @x
--注釋：是用 before 或者 after 關(guān)鍵字代替 into 在指定節(jié)點(diǎn)的 前面 或者 后面 插入同級(jí)節(jié)點(diǎn)
--after 關(guān)鍵字 和 before 關(guān)鍵字不能用于插入屬性
--3、插入屬性 一次插入多個(gè)屬性值/使用變量/屬性定位
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;
GO2、modify（delete ）刪除節(jié)點(diǎn)
xQuery知識(shí)，沒有text()就直接刪除節(jié)點(diǎn)
UPDATE Person
SET Info.modify('
delete (/Person)[1]/Age/text()'
)
where ID = 3實(shí)例：
-- 1、刪除屬性
SET @x.modify('
delete /root/people/student/@id
')
SELECT @x
-- 2、刪除節(jié)點(diǎn)
SET @x.modify('
delete /root/people/student/name[1]
')
SELECT @x
-- 3、刪除節(jié)點(diǎn)內(nèi)容
SET @x.modify('
delete /root/people/student/type/text()
')
SELECT @x
-- 4、刪除所有處理指令
SET @x.modify('
delete //processing-instruction()
')
SELECT @x
-- 5、刪除所有的內(nèi)容為空的節(jié)點(diǎn)
SET @x.modify('
delete //*[empty(./*)]
')
SELECT @x
-----------------------------------------------------------
-- 把 小D 移動(dòng)到 彪 前面
------------------------------------------------------------
SET @x1.modify('
insert /people/student[@name="小D"]
before (/people/student[@name="彪"])[1]
')
SET @x1.modify ('
delete (/people/student[@name="小D"])[2]
')
SELECT @x1
------------------------------------------------------------
-- 把 野子 向前移動(dòng)一級(jí)
------------------------------------------------------------
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
------------------------------------------------------------
-- 把 彪 向后 移一級(jí)
------------------------------------------------------------
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 @x13、modify（replace value of） --更新單個(gè)節(jié)點(diǎn)
在修改語法當(dāng)中 每次只能修改一個(gè)單個(gè)節(jié)點(diǎn)，不能批量修改或者一次修改多個(gè)值，這一點(diǎn)是比較郁悶的
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>';
-- 修改節(jié)點(diǎn)值
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 表達(dá)式
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子句:表數(shù)據(jù)自動(dòng)生成xml格式
通過使用for xml子句，我們可以檢索系統(tǒng)中表的數(shù)據(jù)并自動(dòng)生成xml格式。一共有4種模式：RAW、AUTO、EXPLICIT、PATH。
for xml子句可以用在頂級(jí)查詢和子查詢中，頂級(jí)for xml子句只能出現(xiàn)在select語句中，子查詢中的for xml子句可以出現(xiàn)在insert、delete、update以及賦值語句中。
1、raw模式
raw模式是這4種模式里最簡(jiǎn)單的一種。將為select語句所返回的查詢結(jié)果集中的每一行轉(zhuǎn)換為帶有通用標(biāo)記符“<row>”或可能提供元素名稱的xml元素。
默認(rèn)情況下，行集中非null的列都將映射為<row>元素的一個(gè)屬性。這樣當(dāng)使用select查詢時(shí)，會(huì)對(duì)結(jié)果集進(jìn)行xml的轉(zhuǎn)換，它們將會(huì)被轉(zhuǎn)為row元素的屬性。
select teacherId, teacherName from teacher where teacherSex = '女' for xml raw;
--結(jié)果：<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;
--結(jié)果: <row id="10" name="小李" teacherId="1" teacherName="王靜" />
-- <row id="11" name="小方" teacherId="2" teacherName="李四" />- 如果將 ELEMENTS 指令添加到 FOR XML 子句，則每個(gè)列值都將映射到 <row> 元素的子元素。
- 指定 ELEMENTS 指令之后，您還可以選擇性地指定 XSINIL 選項(xiàng)以將結(jié)果集中的 NULL 列值映射到具有 xsi:nil="true" 屬性的元素。
- 您可以通過向 RAW 模式指定一個(gè)可選參數(shù)為該元素指定另一個(gè)名稱，如該查詢中所示。SELECT * FROM #tb FOR XML RAW('流氓們')
- RAW 模式和 AUTO 模式都可以使用 ROOT , ELEMENTS XSINIL, TYPE 指令。
--> 測(cè)試數(shù)據(jù)：#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模式：表名作為元素名、生成簡(jiǎn)單的層次結(jié)構(gòu)
auto模式也是返回xml數(shù)據(jù)，它與raw的區(qū)別在于返回的xml數(shù)據(jù)中，不是以raw作為元素節(jié)點(diǎn)名，而是使用表名作為元素名。這個(gè)是最明顯的區(qū)別。
除此之外，auto模式的結(jié)果集還可以形成簡(jiǎn)單的層次關(guān)系。
select teacherId, teacherName from teacher where teacherSex = '女' for xml auto;
--結(jié)果：<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;
/* 生成了嵌套關(guān)系
<student id="10" name="小李 ">
<teacher teacherId="1" teacherName="王靜" />
</student>
<student id="11" name="小方 ">
<teacher teacherId="2" teacherName="李四" />
</student>
*/3、path模式
--> 測(cè)試數(shù)據(jù)：#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。 默認(rèn)情況下，針對(duì)行集中的每一行，生成的 XML 中將生成一個(gè)相應(yīng)的 <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、列名以 @ 符號(hào)開頭。
select id as '@id', [name] from #tb for xml path;
--<row id="1"><name>中</name></row><row id="2"><name>美</name></row>
--6、列名不以 @ 符號(hào)開頭
select [name] as 臭流氓 from #tb for xml path('一群流氓');
--<一群流氓><臭流氓>中</臭流氓></一群流氓><一群流氓><臭流氓>美</臭流氓></一群流氓>
--7、列名以 @ 符號(hào)開頭并包含斜杠標(biāo)記 (/)
select id as '@id', [name] as '一群流氓/臭流氓' from #tb for xml path;
--<一群流氓><臭流氓>中</臭流氓></一群流氓><一群流氓><臭流氓>美</臭流氓></一群流氓>
--8、名稱指定為通配符的列
--如果指定的列名是一個(gè)通配符 (*)，則插入此列的內(nèi)容時(shí)就像沒有指定列名那樣插入。
--如果此列不是 xml 類型的列，則此列的內(nèi)容將作為文本節(jié)點(diǎn)插入
select id as '@id', [name] as '*' from #tb for xml path;
--<row id="1">中</row><row id="2">美</row>
--9、列名為 XPath 節(jié)點(diǎn)測(cè)試的列
--text()
--對(duì)于名為 text() 的列，該列中的字符串值將被添加為文本節(jié)點(diǎn)。
--comment()
--對(duì)于名為 comment() 的列，該列中的字符串值將被添加為 XML 注釋。
--node()
--對(duì)于名為 node() 的列，結(jié)果與列名為通配符 (*) 時(shí)相同。
--處理指令（名稱）
--如果列名為處理指令，該列中的字符串值將被添加為此處理指令目標(biāo)名稱的 PI 值。
select id as '@id', '臭流氓' as 'text()', '一個(gè)臭流氓' 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 一個(gè)臭流氓?><!--chouliumang--><EmpName>中</EmpName><臭流氓>中</臭流氓></row>
--10、帶有指定為 data() 的路徑的列名
--如果被指定為列名的路徑為 data()，則在生成的 XML 中，該值將被作為一個(gè)原子值來處理。
--如果序列化中的下一項(xiàng)也是一個(gè)原子值，則將向 XML 中添加一個(gè)空格字符。
--這在創(chuàng)建列表類型化元素值和屬性值時(shí)很有用。 以下查詢將檢索產(chǎn)品型號(hào) ID、名稱和該產(chǎn)品型號(hào)中的產(chǎn)品列表。
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、默認(rèn)情況下，列中的 Null 值映射為“缺少相應(yīng)的屬性、節(jié)點(diǎn)或元素”。
--通過使用 ELEMENTS 指令請(qǐng)求以元素為中心的 XML 并指定 XSINIL 來請(qǐng)求為 NULL 值添加元素，
--可以覆蓋此默認(rèn)行為，如以下查詢所示：
--未指定 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選項(xiàng)
select id as '@id', [name], [type], 0x78786F6F as 'VARBINARY'
from #tb
for xml path, root('oo'), --指定向產(chǎn)生的 XML 中添加單個(gè)頂級(jí)元素。 可以選擇指定要生成的根元素名稱。 默認(rèn)值為“root”。
type, --指定查詢以 xml 類型返回結(jié)果。
binary base64; --如果指定 BINARY Base64 選項(xiàng)，則查詢所返回的任何二進(jìn)制數(shù)據(jù)都用 base64 編碼格式表示。
--若要使用 RAW 和 EXPLICIT 模式檢索二進(jìn)制數(shù)據(jù)，必須指定此選項(xiàng)。
--在 AUTO 模式中，默認(rèn)情況下將二進(jìn)制數(shù)據(jù)作為引用返回。 有關(guān)使用示例，請(qǐng)參閱將 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>到此這篇關(guān)于SQL Server操作XML類型的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
您可能感興趣的文章:
- SQL Server解析XML數(shù)據(jù)的方法詳解
- SQL Server中的XML數(shù)據(jù)進(jìn)行insert、update、delete操作實(shí)現(xiàn)代碼
- SQLServer2005 XML數(shù)據(jù)操作代碼
- 關(guān)于SQLServer2005的學(xué)習(xí)筆記 XML的處理
- SQLSERVER 2005中使用sql語句對(duì)xml文件和其數(shù)據(jù)的進(jìn)行操作(很全面)
- SQL Server中的XML數(shù)據(jù)進(jìn)行insert、update、delete
- SQLServer XML數(shù)據(jù)的五種基本操作
相關(guān)文章
sql convert函數(shù)使用方法小結(jié)整理的比較全了，需要的朋友可以參考下。2009-10-10
圖文教程mssqlserver數(shù)據(jù)庫導(dǎo)出到另外一個(gè)數(shù)據(jù)庫的方法
本篇文章通過圖片并茂的方式給大家介紹mssqlserver數(shù)據(jù)庫導(dǎo)出到另外一個(gè)數(shù)據(jù)庫的方法，對(duì)mssqlserver數(shù)據(jù)庫導(dǎo)出相關(guān)知識(shí)感興趣的朋友一起學(xué)習(xí)吧2015-12-12
數(shù)據(jù)庫初始化及數(shù)據(jù)庫服務(wù)端操作詳解
這篇文章主要為大家介紹了數(shù)據(jù)庫初始化及數(shù)據(jù)庫服務(wù)端操作的過程詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步早日升職加薪2021-11-11
這篇文章主要介紹了Filestream使用簡(jiǎn)單步驟總結(jié),本篇文章通過簡(jiǎn)要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-08-08
本篇文章詳細(xì)講解了SQL ServerSQL的簡(jiǎn)單查詢語句。圖文并茂，方便大家理解與學(xué)習(xí)。有興趣的朋友可以看下2016-12-12
Microsoft Search 服務(wù)無法啟動(dòng) 解決辦法.
嘗試用正常系統(tǒng)的注冊(cè)表項(xiàng)添加到非正常系統(tǒng)中去。（因?yàn)閷?duì)比的兩個(gè)系統(tǒng)版本、結(jié)構(gòu)相同，所此次就直接通過導(dǎo)入導(dǎo)出注冊(cè)表項(xiàng)進(jìn)行批量修改）。2009-04-04
查詢表中某字段有重復(fù)記錄個(gè)數(shù)的方法
本篇文章介紹了，查詢表中某字段有重復(fù)記錄個(gè)數(shù)的方法。需要的朋友參考下2013-04-04
有時(shí)候因?yàn)橐恍?quán)限問題，需要修改表的所有者，下面的方法不錯(cuò)2009-03-03
SQL Server數(shù)據(jù)庫是Microsoft開發(fā)設(shè)計(jì)的一個(gè)關(guān)系數(shù)據(jù)庫智能管理系統(tǒng)(RDBMS),現(xiàn)在是全世界主流數(shù)據(jù)庫之一,下面這篇文章主要給大家介紹了關(guān)于安裝SQL?Server2019詳細(xì)教程,需要的朋友可以參考下2022-11-11
找回SQL企業(yè)管理器里的SQL連接的密碼的方法...2007-03-03