SQL查詢 FOR XML [RAW|AUTO|EXPLICIT]

FOR XML 子句的基本文法
在FOR子句中指定XML模式的基本文法為：
FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64]

參數
XML mode
指定XML模式。XML模式決定所得到的XML的形式。
mode可以是RAW、AUTO或EXPLICIT。

XMLDATA
指定應返回XML-Data架構。文檔的架構被預先設計為內嵌式架構。

ELEMENTS
如果指定ELEMENTS選項，則列作為子項目返回。否則，列將映射到XML特性。只有在AUTO模式下才支援該選項。

BINARY BASE64
如果指定 BINARY Base64 選項，則查詢所返回的任何位元據都用 base64 編碼格式表示。使用RAW和EXPLICIT模式檢索位元據時，必須指定該選項。在AUTO模式中，預設情況下將位元據作為引用返回。

 ===========================================

1.FOR XML RAW
2.FOR XML AUTO
3.FOR XML EXPLICIT
4.更改顯示Tag為中文
5.同表 多層
6.異表 多層
7.處理資料及日期等的Null值
==================
資料庫Person表中的資料為
personName personAge  
lisi                    30 
zhangsan        30 

1. ----------[ RAW ]---------
SELECT [personName], [personAge]
FROM [TestDB].[dbo].[person]
FOR XML RAW

結果:
<row personName="lisi      " personAge="30"/>
<row personName="zhangsan  " personAge="30"/>

2.----------[ AUTO ]--------
SELECT [personName], [personAge]
FROM [TestDB].[dbo].[person]
FOR XML AUTO

結果:
<TestDB.dbo.person personName="lisi      " personAge="30"/>
<TestDB.dbo.person personName="zhangsan  " personAge="30"/>

3.-----------[ EXPLICIT ]--------
select 1 as Tag ,null as parent
,RTRIM(personName) as [PersonBasic!1!personName]
,RTRIM(personAge) as [PersonBasic!1!personAge!xml]
FROM [TestDB].[dbo].[person]
FOR XML EXPLICIT

結果:

<PersonBasic personName="lisi">
   <personAge>30</personAge>
</PersonBasic>
<PersonBasic personName="zhangsan">
   <personAge>30</personAge>
</PersonBasic>

4.----- 更改顯示Tag為中文 -----
select
1 as tag,
null as parent,
personName  as [人員!1!姓名!xml],
personAge  as [人員!1!年齡!xml]
from person
for xml EXPLICIT

結果:

<人員>
<姓名>lisi      </姓名>
<年齡>30</年齡>
</人員>
<人員>
<姓名>zhangsan  </姓名>
<年齡>30</年齡>
</人員>

5. ----- 同表 多層 ----
select
1 as tag,
null as parent,
rtrim(A.personName)   as [人員!1!姓名],
null as [人員資訊!2!年齡!xml]
from person A

union all

select
2 as tag,
1 as parent,
rtrim(A.personName),
B.personAge
from person B,person A
where A.personName=B.personName

order by [人員!1!姓名],tag

for xml EXPLICIT

結果:

<人員 姓名="lisi">
     <人員資訊>
         <年齡>30</年齡>
     </人員資訊>
</人員>
<人員 姓名="zhangsan">
     <人員資訊>
         <年齡>30</年齡>
     </人員資訊>
</人員>

6.-------- 異表 多層 -------
select
1 as tag,
null as parent,
rtrim(A.personName)   as [人員!1!姓名],
null as [人員資訊!2!年齡!xml],
null as [人員資訊!2!職業!xml]
from person A

union all

select
2 as tag,
1 as parent,
rtrim(A.personName),
B.personAge,
rtrim(B.personJob)
from personInfo B,person A
where B.personName=A.personName
order by [人員!1!姓名],tag
for xml EXPLICIT

結果:

<人員 姓名="lisi">
     <人員資訊>
         <年齡>30</年齡>
         <職業>teacher</職業>
     </人員資訊>
</人員>
<人員 姓名="zhangsan">
      <人員資訊>
          <年齡>30</年齡>
          <職業>worker</職業>
      </人員資訊>
</人員>

7.-------處理資料及日期等的Null值-------
資料庫Person表中的資料為
personName personAge   personBirth(可為空白)
lisi                     30     1987-06-06
zhangsan        30     

當資料表中的欄位為Null值時
產生的xml文檔中 將沒有該節點
為瞭解決這一問題
在必要的時候 可以將數字及日期類型
轉換為字串類型
這樣就可以接收空串
(但不知實際運用中效果如何)

SELECT
1 as TAG,
null as parent,
RTRIM(personName)  AS [人員!1!姓名!xml],
RTRIM(ISNULL(CONVERT(CHAR,personAge),''))  AS [人員!1!年齡!xml],
RTRIM(ISNULL(CONVERT(CHAR(10),personBirth,120),'')) AS [人員!1!出生日期!xml]
FROM person
FOR XML EXPLICIT

結果:

<人員>
   <姓名>lisi</姓名>
   <年齡>30</年齡>
   <出生日期>1987-06-06</出生日期>
</人員>
<人員>
   <姓名>zhangsan</姓名>
   <年齡></年齡>
   <出生日期></出生日期>
</人員>

 

轉自:http://www.cnblogs.com/freeliver54/archive/2007/03/22/683563.html

 

====================================

以通用資料庫Northwind為例，下列T-SQL語句可以用XML格式輸出Categories中的ID和Name列表，並為其加上根節點ROOT:

select top 1
1 as Tag,
NULL as Parent,
NULL as [ROOT!1],
CategoryID as [categories!2!CategoryID],
CategoryName as [categories!2!CategoryName]
FROM categories
UNION ALL
select
2,
1,
NULL,
CategoryID,
CategoryName
From Categories
FOR XML EXPLICIT

輸出結果：
<ROOT>
   <categories CategoryID="1" CategoryName="Beverages"/>
   <categories CategoryID="2" CategoryName="Condiments"/>
   <categories CategoryID="3" CategoryName="Confections"/>
   <categories CategoryID="4" CategoryName="Dairy Products"/>
   <categories CategoryID="5" CategoryName="Grains/Cereals"/>
   <categories CategoryID="6" CategoryName="Meat/Poultry"/>
   <categories CategoryID="7" CategoryName="Produce"/>
   <categories CategoryID="8" CategoryName="Seafood"/>
</ROOT>