반응형
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은 (내 테스트에서) 너무 많은 데이터를 가져 오는 것을 사용 하는 것이 좋습니다 .
So, here's my simple example of how you'd create an xml object, then read out its values into a table.
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:
It's bizarre syntax, but with a decent example, it's easy enough to add to your own SQL Server functions.
Speaking of which, here's the correct answer to this question.
Assuming your have your xml data in an @xml variable of type xml (as demonstrated in my example above), here's how you would return the three rows of data from the xml quoted in the question:
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)
SELECT
cast(xmlField as xml).value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
cast(xmlField as xml).value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]
If you are able to wrap your XML in a root element - say then the following is your solution:
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 uses regular XPath rules as follows:
- nodename Selects all nodes with the name "nodename"
- / Selects from the root node
- // Selects nodes in the document from the current node that match the selection no matter where they are
- . Selects the current node
- .. Selects the parent of the current node
- @ Selects attributes
/* This example uses an XML variable with a schema */
IF EXISTS (SELECT * FROM sys.xml_schema_collections
WHERE name = 'OrderingAfternoonTea')
BEGIN
DROP XML SCHEMA COLLECTION dbo.OrderingAfternoonTea
END
GO
CREATE XML SCHEMA COLLECTION dbo.OrderingAfternoonTea AS
N'<?xml version="1.0" encoding="UTF-16" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://Tfor2.com/schemas/actions/orderAfternoonTea"
xmlns="http://Tfor2.com/schemas/actions/orderAfternoonTea"
xmlns:TFor2="http://Tfor2.com/schemas/actions/orderAfternoonTea"
elementFormDefault="qualified"
version="0.10"
>
<xsd:complexType name="AfternoonTeaOrderType">
<xsd:sequence>
<xsd:element name="potsOfTea" type="xsd:int"/>
<xsd:element name="cakes" type="xsd:int"/>
<xsd:element name="fruitedSconesWithCream" type="xsd:int"/>
<xsd:element name="jams" type="xsd:string"/>
</xsd:sequence>
<xsd:attribute name="schemaVersion" type="xsd:long" use="required"/>
</xsd:complexType>
<xsd:element name="afternoonTeaOrder"
type="TFor2:AfternoonTeaOrderType"/>
</xsd:schema>' ;
GO
DECLARE @potsOfTea int;
DECLARE @cakes int;
DECLARE @fruitedSconesWithCream int;
DECLARE @jams nvarchar(128);
DECLARE @RequestMsg NVARCHAR(2048);
DECLARE @RequestXml XML(dbo.OrderingAfternoonTea);
set @potsOfTea = 5;
set @cakes = 7;
set @fruitedSconesWithCream = 25;
set @jams = N'medlar jelly, quince and mulberry';
SELECT @RequestMsg = N'<?xml version="1.0" encoding="utf-16" ?>
<TFor2:afternoonTeaOrder schemaVersion="10"
xmlns:TFor2="http://Tfor2.com/schemas/actions/orderAfternoonTea">
<TFor2:potsOfTea>' + CAST(@potsOfTea as NVARCHAR(20))
+ '</TFor2:potsOfTea>
<TFor2:cakes>' + CAST(@cakes as NVARCHAR(20)) + '</TFor2:cakes>
<TFor2:fruitedSconesWithCream>'
+ CAST(@fruitedSconesWithCream as NVARCHAR(20))
+ '</TFor2:fruitedSconesWithCream>
<TFor2:jams>' + @jams + '</TFor2:jams>
</TFor2:afternoonTeaOrder>';
SELECT @RequestXml = CAST(CAST(@RequestMsg AS VARBINARY(MAX)) AS XML) ;
with xmlnamespaces('http://Tfor2.com/schemas/actions/orderAfternoonTea'
as tea)
select
cast( x.Rec.value('.[1]/@schemaVersion','nvarchar(20)') as bigint )
as schemaVersion,
cast( x.Rec.query('./tea:potsOfTea')
.value('.','nvarchar(20)') as bigint ) as potsOfTea,
cast( x.Rec.query('./tea:cakes')
.value('.','nvarchar(20)') as bigint ) as cakes,
cast( x.Rec.query('./tea:fruitedSconesWithCream')
.value('.','nvarchar(20)') as bigint )
as fruitedSconesWithCream,
x.Rec.query('./tea:jams').value('.','nvarchar(50)') as jams
from @RequestXml.nodes('/tea:afternoonTeaOrder') as x(Rec);
select @RequestXml.query('/*')
참고URL : https://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008
반응형
'Programing' 카테고리의 다른 글
| laravel throwing MethodNotAllowedHttpException (0) | 2020.08.18 |
|---|---|
| PostMan에서 특정 CURL 시뮬레이션 (0) | 2020.08.18 |
| CSS로 무시 하시겠습니까? (0) | 2020.08.18 |
| 친근한 방식으로 ffmpeg 정보 얻기 (0) | 2020.08.18 |
| 알고리즘이 O (log n) 복잡성을 갖게하는 원인은 무엇입니까? (0) | 2020.08.18 |