萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 2005 原生 XML 資料類型與 ADO.NET 2.0 客戶端開發

XML 資料類型:第一類公民

SQL Server 2005 引入原生 `xml` 資料類型,地位等同 `int`、`varchar`。可作為欄位、變數、參數、回傳值。支援:

  • **結構化儲存**:解析為內部節點樹,非單純文字。
  • **XQuery/XPath 查詢**:`query()`、`value()`、`exist()`、`nodes()`、`modify()` (XML DML)。
  • **XML Schema Collection**:`CREATE XML SCHEMA COLLECTION` 註冊 XSD,欄位/變數綁定 Schema 達到型別驗證。
  • **索引**:主 XML 索引 + 路徑/屬性/值次要索引,大幅加速查詢。

FOR XML 增強

  • `FOR XML PATH`:以路徑語法輕鬆建構巢狀 XML,取代複雜的 `EXPLICIT` 模式。
  • `TYPE` 指示詞:直接回傳 `xml` 型別而非字串流,可進一步 `query()`/`value()`。
  • 巢狀 `FOR XML`:子查詢各自產生 XML 片段再組合。
  • `ROOT` 指示詞:產生單一根元素的完整 XML 文件。
  • 預掛 XSD Schema:驗證輸出結構。

ADO.NET 2.0 SqlClient 支援

  • `DbType.Xml` / `SqlDbType.Xml`:參數與欄位型別枚舉新增。
  • `System.Data.SqlTypes.SqlXml`:`xml` 值的包裝器,提供 `CreateReader()` 產生 `XmlReader`。
  • `SqlDataReader.GetSqlXml(ordinal)` / `GetProviderSpecificValue()`:直接取得 `SqlXml`。
  • `DataSet` 的 `DataColumn` 支援 `typeof(SqlXml)`,可離線操作 XML 內容。

客戶端程式碼範例 (C#)

讀取 XML 欄位

using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand("SELECT xmlcol FROM xmltab", conn))
{
    conn.Open();
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            SqlXml sx = rdr.GetSqlXml(0);
            using (XmlReader xr = sx.CreateReader())
            {
                xr.Read();
                Console.WriteLine(xr.ReadOuterXml());
            }
        }
    }
}

寫入 XML 欄位 (參數化)

using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand("INSERT INTO xmltab(xmlcol) VALUES(@x)", conn))
{
    conn.Open();
    cmd.Parameters.Add("@x", SqlDbType.Xml);
    using (XmlReader xr = XmlReader.Create("some.xml"))
        cmd.Parameters[0].Value = new SqlXml(xr);
    cmd.ExecuteNonQuery();
}

Schema 驗證與類型化 XML

-- 伺服器端註冊 Schema
CREATE XML SCHEMA COLLECTION MySchema AS N'...';
-- 建立類型化 XML 欄位
CREATE TABLE TypedXml (id INT PRIMARY KEY, doc XML(MySchema));
-- 插入時自動驗證,不符合 Schema 拋錯

效能與最佳實務

  • **大量 XML 載入**:使用 `SqlBulkCopy` + `SqlXml` 流式寫入,避免記憶體壓力。
  • **查詢效能**:針對常用 XPath 路徑建立 **XML 索引** (主索引 + PATH/VALUE/PROPERTY 次要索引)。
  • **避免 `SELECT *`**:明確列出欄位,減少 XML 序列化開銷。
  • **參數化**:防止 XML 注入,利用查詢計畫快取。

OpenXML vs nodes()

`OPENXML` 需 `sp_xml_preparedocument` 佔用記憶體,適合舊版相容。新開發優先使用 `xml` 型別的 `nodes()` 方法:輕量、無需準備/移除文件控制代碼、可直接 `CROSS APPLY`。

SELECT t.c.value('@id', 'INT'), t.c.value('text()[1]', 'NVARCHAR(100)')
FROM @xmlDoc.nodes('/root/item') AS t(c)
copyright © 萬盛學電腦網 all rights reserved