從 SQL Server 2005 中處理 XML

2024-08-31 00:50:12

字體：大中小

來源：轉載

供稿：網友

收集最實用的網頁特效代碼！

　　一、簡介

　　microsoft sql server 2005 中的重大更改之一是包含了 xml 數據類型。此數據類型是第一類類型，就像 int 或 varchar 一樣，而且 sql server 2005 允許使用一系列 xml 特定的函數對此數據類型進行就地查詢和處理。它還支持存儲數據庫中的 xml 架構的集合，從而啟用基于數據庫的架構驗證。另外，sql server 2005 大大地擴展了 xml 組合（select ... for xml 語句）的功能，擴展了 openxml() xml 分解函數，并針對 xml 數據類型提供了一個新的 nodes() 函數以進行更輕量級的分解。

　　既然對數據庫服務器增強了此新的 xml 功能，那么對 microsoft ado.net 2.0 中的 sqlclient 數據提供程序也進行增強就不會令人感到驚奇了。對 ado.net dataset 也有更改，以便支持類型 xml 的 datacolumn，而且 system.data 和 system.xml 之間的“集成點”得到了拓寬。在本文中，我將探究在客戶端上使用 sql server 2005 xml 數據類型。

　　sql server 2005 可以產生兩類 xml 輸出。語句 select * from authors for xml auto 產生 xml 流，而不是一列一行的行集。該輸出類型與 sql server 2000 中的輸出類型相比沒有改變。只是因為查詢分析器工具中的限制，xml 流輸出在 sql server 查詢分析器中才顯示為一列一行的行集。您可以通過其特定的唯一標識符名稱“xml_f52e2b61-18a1-11d1-b105-000805f49916b”來將這種流與“普通”列區分開來。此名稱實際上是底層 tds（這是一種表格式的數據流，sql server 網絡格式）分析器的指示器，在這種分析器中，列應該流至客戶端，而不是像普通行集那樣發送。有一種特殊的方法 sqlcommand.executexmlreader 用來在客戶端上檢索此特殊的流。在 sql server 2005 中，select ... for xml 語句通過許多方式得到了增強。這里僅提少數幾種：

|||，歡迎訪問網頁設計愛好者web開發。

　　在大多數情況下，當您需要 sql server 2000 中的 for xml explicit 模式時，有一種新的、便于使用的 for xml path 模式。

　　使用 type 指令，除了生成流之外，您還可以生成 xml 數據類型列。

　　可以嵌套 for xml 表達式。

　　select ... for xml 可以使用 root 指令生成 xml 文檔以及 xml 片段。

　　您可以將標準的 xsd 架構預先掛起到流。

　　通過引用 ado.net 2.0 中的關系 datatype 枚舉，您可以初步了解到 xml 是一種一流的關系數據庫類型。system.data.dbtype 和 system.data.sqldbtype 分別包含 dbtype.xml 和 sqldbtype.xml 的附加值。在 system.data.sqltypes 命名空間中也有一個新的類，它是 sqlxml。這個類充當 xml 類型值的 xmlreader 實例工廠。我將通過一些簡單的代碼進行展示。假設我有一份 sql server 表，如下所示：

create table xmltab (
　id int identity primary key,
　xmlcol xml)

　　我可以使用以下 ado.net 2.0 代碼在客戶端上訪問此表。

using system;
using system.data;
using system.data.sqlclient;
using system.data.sqltypes;
using system.xml;
void getxmlcolumn {
// "generic coding..." article for shows how to
// get a connection string from a config file
string s = getconnectstringfromconfigfile("xmldb");
using (sqlconnection conn = new sqlconnection(s))
using (sqlcommand cmd = new sqlcommand(
　　　 "select * from xmltab", conn))
{
　conn.open();
　sqldatareader rdr = cmd.executereader();
　datatable t = rdr.getschematable();
　while (rdr.read())
　　{
　　　sqlxml sx = rdr.getsqlxml(1);
　　　xmlreader xr = sx.createreader();
　　　xr.read();
　　　console.writeline(xr.readouterxml());
　　}
}
}

　　我瀏覽 getschematable 產生的 datatable 時返回的列元數據正確地標識了列：

|||

providertype: 25 (25 = xml)
providerspecificdatatype: system.data.sqltypes.sqlxml
datatype: system.xml.xmlreader
datatypename:

　　正如任何其他構建到 sql server 中的類型一樣。請注意，此列的“.net 類型”是 xmlreader，對于 .net 而言，它就像任何從文件加載或用 xmldocument 類產生的 xml 一樣。在 ado.net 2.0 中的存儲過程或參數化語句中將 xml 數據類型列用作參數同樣簡單：

using system;
using system.data;
using system.data.sqlclient;
using system.data.sqltypes;
using system.xml;
void addarow {
// get a connection string from a config file
string s = getconnectstringfromconfigfile("xmldb");
using (sqlconnection conn = new sqlconnection(s))
using (sqlcommand cmd = new sqlcommand(
　　　 "insert xmltab(xmlcol) values(@x)", conn))
{
　conn.open();
　cmd.parameters.add("@x", sqldbtype.xml);
　// connect the parameter value to a file
　xmlreader xr = xmlreader.create("somexml.xml");
　cmd.parameters[0].value = new sqlxml(xr);
　int i = cmd.executenonquery();
}
}

　　二、是 xml 還是字符串？

　　前面代碼中的兩種方法均使用 sqltypes 中 sql server 特定的數據類型。當我使用 sqlreader 的更一般的訪問器方法 getvalue() 時，值顯著不同。列不是作為 xmlreader 出現，而是作為 .net string 類出現。請注意，即使元數據將列的 .net 數據類型識別為 xmlreader，您也不能將該列強制轉換為 xmlreader。使用除 getsqlxml() 之外任何訪問器都返回字符串。

|||

-- t-sql stored procedure definition
create procedure insert_xml(@x xml)
as
insert xmltab(xmlcol) value(@x)
// client-side code
using system;
using system.data;
using system.data.sqlclient;
void insertxmlfromclient {
// get a connection string from a config file
string s = getconnectstringfromconfigfile("xmldb");
using (sqlconnection conn = new sqlconnection(s))
using (sqlcommand cmd1 = new sqlcommand(
　　　 "insert xmltab(xmlcol) values(@x)", conn))
using (sqlcommand cmd2 = new sqlcommand(
　　　 " insert_xml", conn))
{
　string str = "<somedoc/>";
　conn.open();
　
　// server-side conversion
　cmd1.parameters.add("@x", sqldbtype.nvarchar);
　cmd1.parameters[0].value = str;
　cmd1.executenonquery();
　// client-side conversion works too
　cmd2.commandtype = commandtype.storedprocedure;
　cmd2.parameters.add("@x", sqldbtype.xml);
　cmd2.parameters[0].value = s;
　cmd2.executenonquery();
}
}
三、文檔、片段和 for xml 支持
sql server 2005 中的 xml 數據類型既支持 xml 文檔，也支持 xml 文檔片段。片段與文檔的區別在于，片段能包含多個頂級元素和空文本節點。對于類型化的 xml 列/變量/參數，您可以使用 document（不允許片段）或 content（允許片段）規范指定是否允許片段。content 是默認值，非類型化的 xml 允許片段。以下 t-sql 代碼舉例說明了對片段的支持：
create table xmltab (
　id int identity primary key,
　xmlcol xml)
go
-- insert a document
insert xmltab values('<doc/>')
-- fragment, multiple top-level elements
insert xmltab values('<doc/><doc/>')
-- fragment, bare text node
insert xmltab values('hello world')
-- even this fragment works
insert xmltab values('<doc/>sometext')

　　select ... for xml 也會生成 xml 片段。語句 select job_id, min_lvl, max_lvl from jobs for xml auto 生成以下輸出。請注意，有多個根元素。

|||國內最大的酷站演示中心!

<jobs job_id="1" min_lvl="10" max_lvl="10" />
<jobs job_id="2" min_lvl="200" max_lvl="250" />
<jobs job_id="3" min_lvl="175" max_lvl="225" />
<jobs job_id="4" min_lvl="175" max_lvl="250" />
<!-- some jobs rows deleted for compactness -->

　　使用 sqlxml 既支持文檔，也支持片段。sqlxmlcreatereader() 方法總是創建一個 xmlreader，它通過使用新的 xmlreadersettings 類來支持片段，如下所示：

// pseudocode from sqlxml.createreader
　　stream stm = stm; // stream filled from column (code elided)
　　xmlreadersettings settings = new xmlreadersettings();
　　settings.conformancelevel = conformancelevel.fragment;
　　xmlreader xr = xmlreader.create(
　　　 stm, string.empty, null, null,settings);

　　如果您以同樣的方式構造 xmlreader，則可以在輸入參數中使用 xml 片段。盡管使用 sqlxml 類型時內置了片段支持，但是您在處理包含片段的 xmlreader 時仍需要小心。請注意，調用 xmlreader.getouterxml() 將只提供第一個片段；要定位 xmlreader 以獲取隨后的片段，您必須再次調用 xmlreaderread 方法。我將在本文的稍后部分說明此問題。

　　t-sql 的“select ... for xml”生成了 xml 流，而不是一列一行的行集。它還以二進制格式而非 xml 的標準序列化提供了 xml。由于格式的不同，以及“select ... for xml”總是生成片段，因此使用它需要特殊的方法。出于此目的，sqlclient 實現了一種提供程序特定的方法 sqlcommand.executexmlreader。如果使用 sql server 2000 和 ado 1.0/1.1，則需要使用 executexmlreader 以獲取 for xml 查詢的結果，除非您想使用某些需要字符串連接的相當不好的替代方案。通過 sql server 2005 for xml 增強功能以及對 xml 作為數據類型的支持，您只需使用 executexmlreader 就能從 sql server 獲取單個 xml 流。由于所有為 sql server 2000 編寫的代碼都使用此方法，因此該方法在 ado.net 2.0 中也受支持，而且得到了增強。

|||

　　您可以像在以前的版本中一樣，使用 executexmlreader 從“for xml”查詢中檢索任何流。另外，此方法支持對普通 select 語句生成的 xml 數據類型列進行檢索。這里唯一需要提請您注意的是，當普通 select 語句返回多行時，executexmlreader 僅返回第一行的內容。這里的示例使用與先前示例中相同的表展示了這一點：

using system;
using system.data;
using system.data.sqlclient;
using system.data.sqltypes;
using system.xml;
void useexecxmlreader {
// get a connection string from a config file
string s = getconnectstringfromconfigfile("xmldb");
using (sqlconnection conn = new sqlconnection(s))
using (sqlcommand cmd1 = new sqlcommand(
　　　 "select * from pubs..authors for xml auto,root('root')", conn))
using (sqlcommand cmd2 = new sqlcommand(
　　　 "select * from pubs..authors for xml auto", conn))
using (sqlcommand cmd3 = new sqlcommand(
　　　 "select * from xmltab", conn))
{
　conn.open();
　// contains document
　xmlreader xr1 = cmd1.executexmlreader();
　// contains fragment
　xmlreader xr2 = cmd2.executexmlreader();
　// contains contents of first row in xmltab only
　xmlreader xr3 = cmd3.executexmlreader();
　// use xmlreaders, then
　xr1.dispose(); xr2.dispose(); xr3.dispose();
}
}

　　為了完成關于在 ado.net 2.0 中獲取 xml 的討論，在各種使用方案中提到 xmlreader 內容的生命周期是有好處的。研究 xmlreader 的生命周期還有助于理解 sqlclient 執行的緩沖，以及如何使用此數據獲取最高性能。xmlreader 使用資源，為了釋放這些資源，應該調用 close() 或 dispose() 方法，就像使用 sqlconnection、sqlcommand 和 sqldatareader 一樣。在通過 sqldatareader 讀取 xml 列的情況下，可以為每一行分配一個 xmlreader。請記住，為了支持在同一行的列中向后移動，或者移動到下一行，xmlreader 的內容要在內存中緩沖。當您使用 sqlcommand 的 commandbehavior.sequentialaccess 時，整個 xmlreader 不會在內存中緩沖，但您使用此存取方法時必須小心。使用 commandbehavior.sequentialaccess 時，與列相關聯的 xmlreader 在行集中移動到下一列之前必須完全消耗掉；在移動到下一列之后，xmlreader 看似有效，但調用它的 read() 方法不會產生任何數據。當您使用 executexmlreader 或 executescalar 代替 executereader 時，您無需同樣多的了解此行為，但此時也別忘了關閉/處理 xmlreader。

|||

　　四、在客戶端上使用 xml 架構支持

　　sql server 2005 支持強類型化的 xml，這意味著 xml 必須符合某個 xml 架構或 xml 架構集。通過使用 sql server 中的架構集合可以實現這種支持。xml 架構集合如同任何其他 sql server 對象一樣定義，xml 架構存儲在 sql server 中。t-sql ddl create 語句和 xml 架構集合的用法如下所示：

create xml schema collection books_xsd
as
-- one or more xml schemas here
go
create table typed_xml (
　id int identity primary key,
　-- require books_col content to be schema-valid
　books_col xml(books_xsd)
)
-- validated here
insert typed_xml values('<!-- some document -->')
-- validated here too
update typed_xml
　set books_col.modify('<!-- some xquery dml -->')
　where id = 1

　　當您從客戶端使用 sql server 2005 中強類型化的 xml 數據時，驗證是在服務器上執行，而非在客戶端上執行。例如，如果您使用前述示例中顯示的 addarow 方法向 typed_xml 表添加一行，則在驗證發生之前，數據跨網絡發送到 sql server。然而，只需少量工作，就可以從sql server xml schema collections 檢索 xml 架構，并將它們隱藏在客戶端上以完成客戶端的驗證。這可以防止用戶或 web 服務將架構無效的 xml 發送到用于 sql server 存儲的客戶端，從而節省了一些反復的操作，但是，必須考慮兩處警告/澄清。首先，依賴于從 sql server 所獲取的 xml 架構信息如同依賴于任何高速緩存的客戶端元數據?？赡苣橙艘咽褂?t-sql alter xml schema 語句改變了架構集合，或者甚至刪除了架構集合并重新創建它，從而使得您的客戶端的檢查無用。您可以在 create/alter/drop xml schema ddl 語句上使用新的 event notification，以防止出現異常。然后，您將使用自定義代碼監視 service broker service，這類似于使用帶有查詢通知的 sqlnotificationrequest。event notifications 是 sql server 2005 中的新功能，但它類似于在我前面的文章 ado.net 2.0 中的查詢通知中討論的查詢通知。第二，請記住，即使您在客戶端執行 xml 架構驗證，sql server 也將在服務器上重新檢查。沒有辦法向 sql server 指示“我知道這個 sql server 架構類型化的 xml 實例的架構有效，請不要麻煩地自己檢查了。”

|||

　　要進行客戶端的 xml 架構驗證，您可以使用 t-sql 函數 xml_schema_namespace() 檢索 sql server xml schema collection。這需要 xml 架構集合名稱和數據庫架構名稱，因為 xml 架構集合包括數據庫架構。我們可以將它們硬編碼到程序中，或者從客戶端行集元數據提取它們。通過使用上面的 sqldatareadergetschematable 方法或使用新的 sqlmetadata 類，獲取與一個特定列相關聯的架構集合的名稱是很容易的。這里有一小段代碼示例：

using system;
using system.data;
using system.data.sqlclient;
using system.data.sql;
void getcollectioninfo {
// get a connection string from a config file
string s = getconnectstringfromconfigfile("xmldb");
using (sqlconnection conn = new sqlconnection(s))
using (sqlcommand cmd = new sqlcommand(
　　　 "select books_col from typed_xml", conn))
{
　conn.open();
　// fetch only sql server metadata
　sqldatareader rdr = cmd.executereader(commandbehavior.schemaonly);
　sqlmetadata md = rdr.getsqlmetadata(0);
　string database = md.xmlschemacollectiondatabase;
　string schema = md.xmlschemacollectionowningschema;
　string collection = md.xmlschemacollectionname;
}
}

　　一旦您了解要檢索哪個 xml 架構集合，就可以使用 t-sql 函數將其檢索到客戶端 xmlschemaset 中。請注意，此示例還展示了如何使用 xmlreader 檢索片段。這是必需的，因為 xml schema collection 中的 xml 架構可能不止一個；xml_schema_namespace 函數會將集合中的所有 xml 架構作為一個片段返回。

|||

商業源碼熱門下載www.html.org.cn

using system;
using system.data;
using system.data.sqlclient;
using system.data.sqltypes;
using system.xml;
using system.xml.schema;
void getschemaset {
// get a connection string from a config file
string s = getconnectstringfromconfigfile("xmldb");
using (sqlconnection conn = new sqlconnection(s))
using (sqlcommand cmd = new sqlcommand(
　　　 "select xml_schema_namespace(n'dbo',n'books_xsd')", conn))
{
　xmlschemaset ss = new xmlschemaset();
　conn.open();
　sqldatareader rdr = cmd.executereader();
　rdr.read();
　xmlreader xr = rdr.getsqlxml(0).createreader();
　do
　{
　　ss.add(xmlschema.read(xr, null));
　　xr.read();
　}
　while (xr.nodetype == xmlnodetype.element);
}
}

　　如果手邊有 xmlschemaset，我們就可以將客戶端 xml 驗證代碼集成到 add 例程中。您瞧！這就是客戶端驗證。

void validateandstore(xmlschemaset ss)
{
　// associate the xmlschemaset with the xmlreader
　xmlreadersettings settings = new xmlreadersettings();
　settings.schemas = ss;
　string s = getconnectstringfromconfigfile("xmldb");
　using (xmlreader xr = xmlreader.create(
　　"file://c:/temp/somefile.xml", settings))
　// get a connection string from a config file
　using (sqlconnection conn = new sqlconnection(s))
　using (sqlcommand cmd = new sqlcommand(
　　"insert typed_xml values(@x)", conn))
　{
　　try
　　{
　　　conn.open();
　　　// should throw an exception here if not schema valid
　　　cmd.parameters.addwithvalue("@x", new sqlxml(xr));
　　　int i = cmd.executenonquery();
　　}
　　catch (exception e)
　　{
　　　 console.writeline(e.message);
　　}
　}
}