[sql-server] SQL Server 2008의 XML 필드에서 값 선택

내 XML 필드를 보면 내 행은 다음과 같습니다.

<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person>

이것은 내 테이블에 세 개의 행입니다.

다음과 같이 SQL 결과를 테이블로 반환하고 싶습니다.

Jon  | Johnson
Kathy| Carter
Bob  | Burns

이 작업을 수행하는 쿼리는 무엇입니까?



답변

XML 필드의 이름이 ‘xmlField’인 경우 …

SELECT
[xmlField].value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
[xmlField].value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]


답변

XML 데이터가 ‘테이블’테이블에서 가져오고 ‘필드’열에 저장된다는 점을 고려하면 XML 메서드를 사용 하고을 사용하여 값을 추출하고을 사용하여 xml.value()프로젝트 노드를 xml.nodes()사용 CROSS APPLY하여 조인합니다.

SELECT
    p.value('(./firstName)[1]', 'VARCHAR(8000)') AS firstName,
    p.value('(./lastName)[1]', 'VARCHAR(8000)') AS lastName
FROM table
    CROSS APPLY field.nodes('/person') t(p)

당신은 도랑 수 nodes()cross apply각 필드는 정확히 하나 개의 요소 ‘사람’이 포함 된 경우. XML이 선택한 변수 FROM @variable.nodes(...)이고 cross apply.


답변

이 게시물은 XML 형식이 약간 다른 문제를 해결하는 데 도움이되었습니다. 내 XML에는 다음 예제와 같은 키 목록이 포함되어 있으며 DeleteBatch라는 테이블의 SourceKeys 열에 XML을 저장합니다.

<k>1</k>
<k>2</k>
<k>3</k>

테이블을 만들고 일부 데이터로 채 웁니다.

CREATE TABLE dbo.DeleteBatch (
    ExecutionKey INT PRIMARY KEY,
    SourceKeys XML)

INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 1,
    (CAST('<k>1</k><k>2</k><k>3</k>' AS XML))

INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 2,
    (CAST('<k>100</k><k>101</k>' AS XML))

XML에서 키를 선택하는 SQL은 다음과 같습니다.

SELECT ExecutionKey, p.value('.', 'int') AS [Key]
FROM dbo.DeleteBatch
    CROSS APPLY SourceKeys.nodes('/k') t(p)

다음은 쿼리 결과입니다.

ExecutionKey 키
1 1
1 2
1 3
2100
2101


답변

이것은 귀하의 질문에 답할 수 있습니다.

select cast(xmlField as xml) xmlField into tmp from (
select '<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>' xmlField
union select '<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>'
union select '<person><firstName>Bob</firstName><lastName>Burns</lastName></person>'
) tb

SELECT
    xmlField.value('(person/firstName)[1]', 'nvarchar(max)') as FirstName
    ,xmlField.value('(person/lastName)[1]', 'nvarchar(max)') as LastName
FROM tmp

drop table tmp


답변

Blimey. 이것은 발견하기에 정말 유용한 스레드였습니다.

여전히 이러한 제안 중 일부가 혼란스러워졌습니다. 문자열에서 valuewith [1]를 사용할 때마다 첫 번째 값만 검색됩니다. 그리고 몇 가지 제안 cross apply은 (내 테스트에서) 너무 많은 데이터를 가져 오는 것을 사용 하는 것이 좋습니다 .

여기에 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)

다음은 출력입니다.

여기에 이미지 설명 입력

기괴한 구문이지만 괜찮은 예를 들어 SQL Server 함수에 쉽게 추가 할 수 있습니다.

여기에, 말하자면은의 올바른 이 질문에 대한 대답.

위의 예에서 설명한 것처럼 @xml유형 의 변수에 xml 데이터가 있다고 가정하면 다음은 xml질문에 인용 된 xml에서 세 행의 데이터를 반환하는 방법입니다.

SELECT
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName'
FROM @xml.nodes('/person') as x(Rec)

여기에 이미지 설명 입력


답변

XML을 루트 요소로 래핑 할 수 있다면 다음과 같은 솔루션이 있습니다.

DECLARE @PersonsXml XML = '<persons><person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person></persons>'

SELECT  b.value('(./firstName/text())[1]','nvarchar(max)') as FirstName, b.value('(./lastName/text())[1]','nvarchar(max)') as LastName
FROM @PersonsXml.nodes('/persons/person') AS a(b)

여기에 이미지 설명 입력


답변

MSSQL은 다음과 같이 일반 XPath 규칙을 사용합니다.

  • nodename 이름이 “nodename”인 모든 노드를 선택합니다.
  • / 루트 노드에서 선택
  • // 현재 노드에서 문서의 위치에 관계없이 선택 항목과 일치하는 노드를 선택합니다.
  • . 현재 노드를 선택합니다.
  • .. 현재 노드의 부모 선택
  • @ 속성 선택

W3Schools