SQL Server의 테이블에서 Xml 값 및 속성을 쿼리하는 방법은 무엇입니까?

SQL Server의 테이블에서 Xml 값 및 속성을 쿼리하는 방법은 무엇입니까?

다음이 포함된 테이블이 있습니다.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 [제곱 데이터].query():특성은 요소 외부에 나타날 수 없습니다.

저는 사냥을 해봤는데, XML 쿼리가 문서화되어 있지 않거나, 예를 들어, 얼마나 엉망인지 놀랍습니다.테이블을 쿼리하는 대신 대부분의 리소스는 변수를 쿼리합니다. 하지만 저는 그렇게 하지 않습니다.대부분의 리소스는 값을 읽기보다는 필터링 및 선택에 xml 쿼리만 사용합니다.대부분의 리소스는 실제 값이 아닌 하드 코딩된 하위 노드(인덱스 기준)를 읽습니다.

읽은 관련 리소스

• https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008
• 요소 값에 대한 SQL Server 쿼리 xml 특성
• SQL XML 특성 쿼리
• SQL Server 2005 XQuery 및 XML-DML - 1부
• BOL: Microsoft SQL Server 2005의 XML 지원
• SQL Server에서 XML 쿼리 중
• 기본 SQL Server XML 쿼리
• BOL: query() 메서드(xml 데이터 유형)
• XML 워크샵 V - XML 열에서 값 읽기
• SQL SERVER – 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, 레벨 16, 상태 1, 라인 3 XQuery [Sqm.data.value()]:
'value()'에는 싱글톤(또는 빈 시퀀스)이 필요합니다. 'xdt:untypedAtomic *' 유형의 피연산자가 발견되었습니다.

실제로 목표에 근접했습니다. 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 fiddle demo

저는 노드를 사용하지 않고 아주 비슷한 것을 하려고 노력해 왔습니다.하지만 제 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

나에게 이것은 외부 적용 또는 교차 적용을 사용하는 것보다 훨씬 덜 혼란스럽습니다.

이것이 더 간단한 해결책을 찾는 다른 사람에게 도움이 되기를 바랍니다!

사용하다value에 query의 인덱스를 두 매개 을 전달해야 합니다XQuery에서 반환할 의 인덱스를 지정해야 합니다.

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

왜 어떤 사람들이 사용을 제안하는지 이해할 수 없습니다.cross apply또는outer apply을 valuesxml 합니다.저에게는, 그것이 너무 많은 데이터를 가져왔습니다.

다음은 시스템을 생성하는 방법에 대한 예입니다.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)

결과는 다음과 같습니다.

언급URL : https://stackoverflow.com/questions/19165213/how-to-query-for-xml-values-and-attributes-from-table-in-sql-server