使用OPENXML函數將XML文檔轉換為行結果集 [ZT]

FOR XML子句都是將行結果集轉換為XML結果集，那麼如果想要將XML文檔轉換成行結果集，這時就要使用OPENXML函數。OPENXML在SQL Server 2000中就已經提供，但是在SQL Server 2005對該函數進行了增強。
OPENXML的文法為：

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] ) 
[ WITH ( SchemaDeclaration | TableName ) ]

第一個參數idoc是XML文檔的控制代碼，該控制代碼需要通過調用 sp_xml_preparedocument 建立 XML 文檔的內部表式形式來獲得。參數rowpattern是一個XPATH模式，用來標識要作處理的節點。第三個參數中用1表示查詢以屬性為中心，2表示查詢以元素為中心。最後WITH子句標識出要返回的欄位。
使用OPENXML函數將該XML文檔轉換為行結果集的代碼:

declare @mydoc xml
set @mydoc='
<Person>
  <row FirstName="Gustavo" LastName="Achong" />
  <row FirstName="Catherine" MiddleName="R." LastName="Abel" />
</Person>
'--定義XML文檔
declare @docHandle int
Exec sp_xml_preparedocument @docHandle OUTPUT,@mydoc
--獲得XML文檔的控制代碼
SELECT * FROM OPENXML(@docHandle,'/Person/row',1)--1表示以屬性為中心
WITH (FirstName nvarchar(50),MiddleName nvarchar(50),LastName nvarchar(50))

啟動並執行結果為：
FirstName         MiddleName          LastName
----------------  ----------------    ----------------
Gustavo           NULL                Achong
Catherine         R.                  Abel
若將代碼中OPENXML函數的第三個參數換為2那麼將返回2行NULL值，因為2表示查詢以元素為中心，而row節點下沒有其他元素。同樣的道理，如果給出的XML文檔只有元素而沒有屬性，那麼就要使用參數2而不能使用1 。那麼如果想要查詢出的資料一部分在元素的屬性中，一部分在元素的子項目中那麼我們可以將該參數換成3。查詢語句及返回結果見代碼：

declare @mydoc xml
set @mydoc='
<Products>
  <Product Category="Book">
    <Name>Windows 2008</Name>
    <Vendor>Vendor1</Vendor>
  </Product>
  <Product Category="Book">
    <Name>SQL2008</Name>
    <Vendor>Vendor2</Vendor>
  </Product>
</Products>'
declare @docHandle int
Exec sp_xml_preparedocument @docHandle OUTPUT,@mydoc
SELECT * FROM OPENXML(@docHandle,'/Products/Product',3)
WITH (Category nvarchar(50),Name nvarchar(50),Vendor nvarchar(50))

實際上第三個參數無論跟任何正整數SQL Server 2005都不會報錯，微軟官方MSDN中只給出了0、1、2、8這4個數位意思。其實SQL Server 2005內部是根據第三個參數的二進位位元位上的值來確定查詢的方式的。最後2位是00或01（比如：0、1、4、5等）就是以屬性為中心進行查詢，最後2為是10（比如：2、6、10等）就是以元素為中心的查詢，而最後2位是11（比如：3、7等）就表示既要查詢屬性也要查詢元素。