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 쿼리는 작동하지 않습니다.
메시지 2396, 수준 16, 상태 1, 줄 2
XQuery [Sqm.data.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 Workshop 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, Level 16, State 1, Line 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)
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)
I don't understand why some people are suggesting using cross apply or outer apply to convert the xml into a table of values. For me, that just brought back way too much data.
Here's my example of how you'd create an xml object, then turn it into a table.
(I've added spaces in my xml string, just to make it easier to read.)
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)
And here's the output:
I've been trying to do something very similar but not using the nodes. However, my xml structure is a little different.
You have it like this:
<Metrics>
<Metric id="TransactionCleanupThread.RefundOldTrans" type="timer" ...>
If it were like this instead:
<Metrics>
<Metric>
<id>TransactionCleanupThread.RefundOldTrans</id>
<type>timer</type>
.
.
.
Then you could simply use this SQL statement.
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
To me this is much less confusing than using the outer apply or cross apply.
I hope this helps someone else looking for a simpler solution!
'IT TIP' 카테고리의 다른 글
| JPA 엔티티 관리자를 닫아야합니까? (0) | 2020.10.29 |
|---|---|
| IPython에서 매직 '실행'함수에 변수를 전달하는 방법 (0) | 2020.10.29 |
| OSX에서 클래스 경로에 추가 (0) | 2020.10.29 |
| Visual Studio에서 여러 줄 찾기 및 바꾸기 (0) | 2020.10.29 |
| C에서 배열을 반환 할 수 없다는 것은 실제로 무엇을 의미합니까? (0) | 2020.10.29 |