使用OPENXML函數将XML文檔轉換為行結果集

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。查詢語句及傳回結果見代碼：

<Products>

  <Product Category="Book">

    <Name>Windows 2008</Name>

    <Vendor>Vendor1</Vendor>

  </Product>

    <Name>SQL2008</Name>

    <Vendor>Vendor2</Vendor>

</Products>'

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等）就表示既要查詢屬性也要查詢元素。

本文轉自深藍居部落格園部落格，原文連結：http://www.cnblogs.com/studyzy/archive/2007/12/10/988806.html，如需轉載請自行聯系原作者