1. ホーム
  2. sql-server

[解決済み] SQL Server のテーブルから Xml 値と属性を検索するには?

2023-01-11 05:30:05

質問

私は、テーブルの中に Xml カラムがあります。

SELECT * 
FROM Sqm

<イグ

のサンプルは xml のデータは、1つの行になります。

<Sqm version="1.2">
  <Metrics>
    <Metric id="TransactionCleanupThread.RecordUsedTransactionShift" type="timer" unit="µs" count="1" sum="21490"   average="21490"   minValue="73701"    maxValue="73701"                               >73701</Metric>
    <Metric id="TransactionCleanupThread.RefundOldTrans"             type="timer" unit="µs" count="1" sum="184487"  average="184487"  minValue="632704"   maxValue="632704"                              >632704</Metric>
    <Metric id="Database.CreateConnection_SaveContextUserGUID"       type="timer" unit="µs" count="2" sum="7562"    average="3781"    minValue="12928"    maxValue="13006"    standardDeviation="16"     >12967</Metric>
    <Metric id="Global.CurrentUser"                                  type="timer" unit="µs" count="6" sum="4022464" average="670411"  minValue="15"       maxValue="13794345" standardDeviation="1642047">2299194</Metric>
    <Metric id="Global.CurrentUser_FetchIdentityFromDatabase"        type="timer" unit="µs" count="1" sum="4010057" average="4010057" minValue="13752614" maxValue="13752614"                            >13752614</Metric>
  </Metrics>
</Sqm>

このデータの場合、私が欲しいのは

SqmId  id                                                   type   unit  count  sum      minValue  maxValue  standardDeviation  Value
=====  ===================================================  =====  ====  =====  ======   ========  ========  =================  ======
1      TransactionCleanupThread.RecordUsedTransactionShift  timer  µs    1      21490    73701     73701     NULL               73701
1      TransactionCleanupThread.RefundOldTrans              timer  µs    1      184487   632704    632704    NULL               632704
1      Database.CreateConnection_SaveContextUserGUID        timer  µs    2      7562     12928     13006     16                 12967
1      Global.CurrentUser                                   timer  µs    6      4022464  15        13794345  1642047            2299194
1      Global.CurrentUser_FetchIdentityFromDatabase         timer  µs    1      4010057  13752614  13752614  NULL               13752614
2      ...

最終的には実際に演奏する SUM() , MIN() , MAX() のアグリゲーションになります。しかし、今のところ、私は クエリ xmlカラムをクエリすることです。

擬似コードで言うと、こんな感じにしてみます。

SELECT
    SqmId,
    Data.query('/Sqm/Metrics/Metric/@id') AS id,
    Data.query('/Sqm/Metrics/Metric/@type') AS type,
    Data.query('/Sqm/Metrics/Metric/@unit') AS unit,
    Data.query('/Sqm/Metrics/Metric/@sum') AS sum,
    Data.query('/Sqm/Metrics/Metric/@count') AS count,
    Data.query('/Sqm/Metrics/Metric/@minValue') AS minValue,
    Data.query('/Sqm/Metrics/Metric/@maxValue') AS maxValue,
    Data.query('/Sqm/Metrics/Metric/@standardDeviation') AS standardDeviation,
    Data.query('/Sqm/Metrics/Metric') AS value
FROM Sqm

しかし、そのSQLクエリはうまくいきません。

Msg 2396, レベル 16, ステート 1, 行 2

XQuery [Sqm.data.query()] を参照してください。属性は要素の外側に表示されない可能性があります。

Xmlクエリのドキュメントがいかに貧弱であるか、また、Xmlクエリの例がいかに少ないかに驚かされました。ほとんどのリソースは テーブル をクエリするよりも、むしろ 変数 というようなことはしていません。ほとんどのリソースは、値を読み取るのではなく、フィルタリングと選択のためにのみxmlクエリを使用します。ほとんどのリソースは、実際の値ではなく、ハードコードされた子ノード(インデックスによる)を読み取ります。

私が読んだ関連リソース

更新: .queryではなく.value

をランダムに使ってみました。 .value の代わりに .query :

SELECT
    Sqm.SqmId,
    Data.value('/Sqm/Metrics/Metric/@id', 'varchar(max)') AS id,
    Data.value('/Sqm/Metrics/Metric/@type', 'varchar(max)') AS type,
    Data.value('/Sqm/Metrics/Metric/@unit', 'varchar(max)') AS unit,
    Data.value('/Sqm/Metrics/Metric/@sum', 'varchar(max)') AS sum,
    Data.value('/Sqm/Metrics/Metric/@count', 'varchar(max)') AS count,
    Data.value('/Sqm/Metrics/Metric/@minValue', 'varchar(max)') AS minValue,
    Data.value('/Sqm/Metrics/Metric/@maxValue', 'varchar(max)') AS maxValue,
    Data.value('/Sqm/Metrics/Metric/@standardDeviation', 'varchar(max)') AS standardDeviation,
    Data.value('/Sqm/Metrics/Metric', 'varchar(max)') AS value
FROM Sqm

しかし、これもうまくいきません。

Msg 2389, Level 16, State 1, Line 3 XQuery [Sqm.data.value()]:

'value()' はシングルトン(または空のシーケンス)を必要とし、見つかったオペランドは タイプ 'xdt:untypedAtomic *' が必要です。

どのように解決するのですか?

実際、あなたは目標に近づいています。 ノード() メソッドを使って行を分割し、値を取得するだけです。

select
    s.SqmId,
    m.c.value('@id', 'varchar(max)') as id,
    m.c.value('@type', 'varchar(max)') as type,
    m.c.value('@unit', 'varchar(max)') as unit,
    m.c.value('@sum', 'varchar(max)') as [sum],
    m.c.value('@count', 'varchar(max)') as [count],
    m.c.value('@minValue', 'varchar(max)') as minValue,
    m.c.value('@maxValue', 'varchar(max)') as maxValue,
    m.c.value('.', 'nvarchar(max)') as Value,
    m.c.value('(text())[1]', 'nvarchar(max)') as Value2
from sqm as s
    outer apply s.data.nodes('Sqm/Metrics/Metric') as m(c)

sql フィドルデモ