XQueryを使ってSQL ServerのXMLデータから情報を抽出する

2024-05-20

SQL ServerのXML列の値をクエリする方法

XQueryを使用する

XQueryは、XMLドキュメントをクエリするための言語です。SQL Serverでは、query()メソッドを使用して、XML列に対してXQueryを実行することができます。

SELECT
  ProductID,
  ProductDescription.value('(Features/Feature/@Name)[1]') AS FeatureName
FROM
  Products

このクエリは、Productsテーブルの各製品のProductIDと、ProductDescription XML列にある最初の<Feature>要素の@Name属性の値を選択します。

value()メソッドは、XML列からスカラ値を抽出するために使用することができます。

SELECT
  ProductID,
  ProductDescription.value('//ProductDescription/Price') AS Price
FROM
  Products

XML PATHは、XMLドキュメント内の特定のノードまたは値を識別するために使用される構文です。SQL Serverでは、CAST()関数を使用して、XML列をXML PATHに変換することができます。

SELECT
  ProductID,
  CAST(ProductDescription AS xml).value('/ProductDescription/Price') AS Price
FROM
  Products

その他のメソッド

SQL Serverには、XML列を操作するための他にも多くのメソッドがあります。詳細については、Microsoftのドキュメントを参照してください：https://learn.microsoft.com/en-us/sql/relational-databases/xml/xml-data-sql-server?view=sql-server-ver16

例

上記の例の他にも、以下の例もご参考ください。

特定の要素のすべての値を取得する
XML列を別のデータ型に変換する
複数のXML列を結合する

SQL Serverには、XML列の値をクエリするための様々な方法があります。上記の例を参考に、ニーズに合った方法を選択してください。

サンプルコード：SQL ServerでXML列の値をクエリする

<Product>
  <Name>Product Name</Name>
  <Description>Product description</Description>
  <Price>19.99</Price>
  <Features>
    <Feature Name="Color">Red</Feature>
    <Feature Name="Size">Small</Feature>
  </Features>
</Product>

SELECT
  ProductID,
  ProductDescription.value('//ProductDescription/Price') AS Price
FROM
  Products

SELECT
  ProductID,
  ProductDescription.value('(Features/Feature/@Name)[1]') AS FeatureName
FROM
  Products

SELECT
  ProductID,
  CAST(ProductDescription AS varchar(MAX)) AS ProductDescriptionText
FROM
  Products

SELECT
  p.ProductID,
  c.CategoryName,
  CAST(p.ProductDescription AS varchar(MAX)) AS ProductDescriptionText
FROM
  Products p
INNER JOIN
  Categories c
ON
  p.CategoryID = c.CategoryID

説明

上記の例では、以下の点に注意する必要があります。

ProductDescription列はXMLデータ型である必要があります。
使用するXQuery式は、目的のデータを取得するために正しく記述されている必要があります。
必要に応じて、CAST()関数を使用して、XML列を別のデータ型に変換することができます。
複数のXML列を結合する場合は、適切なテーブル結合を使用する必要があります。

SQL ServerでXML列をクエリするその他の方法

FOR XML句は、SELECTステートメントの結果をXML形式で取得するために使用することができます。この句を使用すると、XMLドキュメントの構造と内容を細かく制御することができます。

SELECT
  ProductID,
  ProductDescription,
  FOR XML PATH('Products')
FROM
  Products

XML ビューを使用する

XMLビューは、基盤となるテーブルのデータをXML形式で格納する特殊なタイプのビューです。XMLビューを使用すると、XQueryを使用してビューに対してクエリを実行することができます。

CREATE XML VIEW MyProducts AS
SELECT
  ProductID,
  ProductDescription
FROM
  Products

このクエリは、MyProductsという名前のXMLビューを作成します。このビューに対して、以下のXQueryを使用してクエリを実行することができます。

SELECT
  ProductID,
  ProductDescription.value('//ProductDescription/Price') AS Price
FROM
  MyProducts

OPENXMLを使用する

OPENXMLは、SQL Serverを使用してXMLドキュメントを開き、処理するためのAPIです。OPENXMLを使用すると、XMLドキュメントをXMLノードの階層として操作することができます。

DECLARE @xmlDoc xml

SET @xmlDoc =
  OPENXML(
    (
      SELECT
        ProductDescription
      FROM
        Products
      WHERE
        ProductID = 123
    ),
    TYPE = 2
  )

SELECT
  @xmlDoc.value('//ProductDescription/Price') AS Price

このクエリは、ProductIDが123である製品のProductDescription XML列を取得し、そのXML列にあるPrice要素の値を返します。

SQLCLRを使用する

SQLCLRは、SQL Server内でCLR(Common Language Runtime)コードを実行できるようにする機能です。SQLCLRを使用して、C#などの.NET言語で書かれたカスタムXML関数を作成することができます。

using System;
using System.Data.SqlClient;
using System.Xml;

public class ProductPrice
{
  public static decimal GetPrice(int productId)
  {
    using (SqlConnection connection = new SqlConnection("connection string"))
    {
      connection.Open();

      using (SqlCommand command = new SqlCommand("SELECT ProductDescription FROM Products WHERE ProductID = @ProductID", connection))
      {
        command.Parameters.AddWithValue("@ProductID", productId);

        using (SqlDataReader reader = command.ExecuteReader())
        {
          if (reader.Read())
          {
            XmlDocument doc = new XmlDocument();
            doc.LoadXml((string)reader["ProductDescription"]);

            return decimal.Parse(doc.SelectSingleNode("//ProductDescription/Price").InnerText);
          }
          else
          {
            throw new Exception("Product not found.");
          }
        }
      }
    }
  }
}

このC#コードは、ProductPriceというクラスに、GetPriceというメソッドを定義します。このメソッドは、製品IDに基づいて製品価格を取得します。このメソッドをSQL Serverで使用するには、以下のクエリを実行する必要があります。

SELECT
  ProductPrice.GetPrice(ProductID) AS Price
FROM
  Products

SQL ServerでXML列をクエリする方法は様々です。それぞれの方法には、それぞれ長所と短所があります。ニーズに合った方法を選択することが重要です。

[A. 次の例では、xml 型の変数 @myDoc を宣言し、XML インスタンスをこれに代入します。その後 query() メソッドを使用して、ドキュメントに対して XQuery を指定します。次のクエリは、< ProductDescription > 要素の < Features > 子要素を取得

sql sql-server xml