Как запросить значения и атрибуты Xml из таблицы в SQL Server?

У меня есть таблица, содержащая столбец Xml:

SELECT * 
FROM Sqm

введите описание изображения здесь

Пример xml данных строки:

<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-запросы только для фильтрации и выбора, а не для чтения значений. Большинство ресурсов считывают жестко запрограммированные дочерние узлы (по индексу), а не фактические значения.

Связанные ресурсы, которые я прочитал

Обновление: .value, а не .query

Я пробовал случайно использовать .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, уровень 16, состояние 1, строка 3 XQuery [Sqm.data.value ()]:
'value ()' требует одноэлементного элемента (или пустой последовательности), найден операнд типа 'xdt: untypedAtomic *'


xml sql-server sql-server-2008-r2 sql-server-2005 sqlxml
person Ian Boyd    schedule 03.10.2013    source источник

Ответы (4)


arrow_upward
120
arrow_downward

На самом деле вы близки к своей цели, вам просто нужно использовать метод nodes () для разделения ваши строки, а затем получить значения:

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

person Roman Pekar    schedule 03.10.2013
comment
Как мне получить значение самого узла? Кажется, нет никакого способа select m.* увидеть созданную им секретную магическую промежуточную таблицу. Какой синтаксис для запроса значения элемента? например значение <Metric>8675309</Metric> равно 8675309 - person Ian Boyd; 03.10.2013
comment
@IanBoyd извините, пропустил, см. Обновлено. Вы можете использовать '.' или текст, если могут быть вложенные элементы - person Roman Pekar; 03.10.2013
comment
Что означают псевдонимы s, m и c в этом запросе? - person Ian R. O'Brien; 10.11.2016
comment
@ IanR.O'Brien m - набор результатов, возвращаемый функцией nodes(), s - это sqm сама таблица, c - столбец с типом данных xml в наборе результатов, возвращенном функцией nodes() - person Roman Pekar; 10.11.2016
comment
Это потрясающе - person Sarath Mohandas; 28.01.2021

arrow_upward
12
arrow_downward

Я пытался сделать что-то очень похожее, но не используя узлы. Однако моя структура xml немного отличается.

У вас это так:

<Metrics>
    <Metric id="TransactionCleanupThread.RefundOldTrans" type="timer" ...>

Если бы это было так:

<Metrics>
    <Metric>
        <id>TransactionCleanupThread.RefundOldTrans</id>
        <type>timer</type>
        .
        .
        .

Тогда вы могли бы просто использовать этот оператор SQL.

SELECT
    Sqm.SqmId,
    Data.value('(/Sqm/Metrics/Metric/id)[1]', 'varchar(max)') as id,
    Data.value('(/Sqm/Metrics/Metric/type)[1]', 'varchar(max)') AS type,
    Data.value('(/Sqm/Metrics/Metric/unit)[1]', 'varchar(max)') AS unit,
    Data.value('(/Sqm/Metrics/Metric/sum)[1]', 'varchar(max)') AS sum,
    Data.value('(/Sqm/Metrics/Metric/count)[1]', 'varchar(max)') AS count,
    Data.value('(/Sqm/Metrics/Metric/minValue)[1]', 'varchar(max)') AS minValue,
    Data.value('(/Sqm/Metrics/Metric/maxValue)[1]', 'varchar(max)') AS maxValue,
    Data.value('(/Sqm/Metrics/Metric/stdDeviation)[1]', 'varchar(max)') AS stdDeviation,
FROM Sqm

Для меня это гораздо менее запутанно, чем использование внешнего или перекрестного применения.

Надеюсь, это поможет кому-то другому, ищущему более простое решение!

person Ryan Dorendorf    schedule 13.12.2016
comment
в коде отсутствуют открывающие скобки. также добавьте /text() после идентификатора и т. д. для увеличения производительности - person Danny Rancher; 04.10.2017
comment
Это самый простой способ. Спасибо, сработало безупречно. - person S.E.; 20.10.2017
comment
Как при таком подходе запросить таблицу со столбцом типа XML? Спасибо. - person FMFF; 07.05.2020

arrow_upward
10
arrow_downward

используйте value вместо query (необходимо указать индекс узла для возврата в XQuery, а также передать тип данных sql для возврата в качестве второго параметра):

select
    xt.Id
    , x.m.value( '@id[1]', 'varchar(max)' ) MetricId
from
    XmlTest xt
    cross apply xt.XmlData.nodes( '/Sqm/Metrics/Metric' ) x(m)
person Moho    schedule 03.10.2013

arrow_upward
9
arrow_downward

Я не понимаю, почему некоторые люди предлагают использовать cross apply или outer apply для преобразования xml в таблицу значений. Для меня это просто принесло слишком много данных.

Вот мой пример того, как вы создадите объект xml, а затем превратите его в таблицу.

(Я добавил пробелы в свою строку xml, чтобы ее было легче читать.)

DECLARE @str nvarchar(2000)

SET @str = ''
SET @str = @str + '<users>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mike</firstName>'
SET @str = @str + '     <lastName>Gledhill</lastName>'
SET @str = @str + '     <age>31</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mark</firstName>'
SET @str = @str + '     <lastName>Stevens</lastName>'
SET @str = @str + '     <age>42</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Sarah</firstName>'
SET @str = @str + '     <lastName>Brown</lastName>'
SET @str = @str + '     <age>23</age>'
SET @str = @str + '  </user>'
SET @str = @str + '</users>'

DECLARE @xml xml
SELECT @xml = CAST(CAST(@str AS VARBINARY(MAX)) AS XML) 

--  Iterate through each of the "users\user" records in our XML
SELECT 
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName',
    x.Rec.query('./age').value('.', 'int') AS 'Age'
FROM @xml.nodes('/users/user') as x(Rec)

И вот результат:

введите описание изображения здесь

person Mike Gledhill    schedule 08.09.2016
comment
Любопытно ... почему вложенное приведение к Varbinary(max) перед приведением xml, пожалуйста? - person EvilDr; 26.09.2019
comment
Как при таком подходе запросить таблицу со столбцом типа XML? Спасибо. - person FMFF; 07.05.2020
comment
@FMFF этот метод фактически идентичен всем остальным, использующим перекрестное применение. - person SgtWilko; 03.12.2020