SQL Server 2008의 XML 필드에서 값 선택

code

SQL Server 2008의 XML 필드에서 값 선택

starcafe 2023. 4. 28. 21:12

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 데이터가 테이블 'table'에서 가져온 다음 'field' 열에 저장된다는 점을 고려하여 XML 메서드를 사용하고, , 프로젝트 노드를 사용하여 , 를 사용하여 결합합니다.

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에는 다음 예제와 같은 키 목록이 포함되어 있으며 XML을 원본 키 열에 DeleteBatch라는 테이블에 저장합니다.

<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)

다음은 쿼리 결과입니다...

실행 키 키1   11   21   32   1002   101

다음과 같은 질문에 답할 수 있습니다.

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

세상에, 이건 발견하기에 정말 유용한 실이었어요

저는 여전히 이 제안들 중 일부가 혼란스럽다는 것을 알았습니다.사용할 때마다value와 함께[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)

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]

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" 이름을 가진 모든 노드를 선택합니다.
루트 노드에서 선택합니다.
현재 노드에서 선택한 노드와 일치하는 노드를 문서에서 선택합니다. 노드의 위치에 상관없이 선택합니다.
현재 노드를 선택합니다.
현재 노드의 상위 노드를 선택합니다.
특성을 선택합니다.

W3 학교

이 예제에서는 스키마가 */인 XML 변수를 사용합니다.

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('/*')

다음 샘플을 사용할 수 있습니다.

DECLARE @myDoc XML  
DECLARE @ProdID INT  
SET @myDoc = '<Root>  
<ProductDescription ProductID="1" ProductName="Road Bike">  
<Features>  
  <Warranty>1 year parts and labor</Warranty>  
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>  
</Features>  
</ProductDescription>  
</Root>'  
  
SET @ProdID =  @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )  
SELECT @ProdID

나의 경우 xml 분쇄는 잘 작동합니다: https://dzone.com/articles/dude-lets-xml-shred

XML:

DECLARE @x XML
SET @x = '
<Library>
  <Books>
    <Book Type="Paperback">
      <Author>Robert Jordan</Author>
      <Id>26</Id>
      <PublicationDate>01/15/1990</PublicationDate>
      <Series>
        <Name>The Wheel of Time</Name>
        <Number>1</Number>
      </Series>
      <Title Chapters="53">The Eye of the World</Title>
    </Book>
    <Book Type="Hardback">
      <Author>Robert Jordan</Author>
      <Id>87</Id>
      <PublicationDate>09/15/1992</PublicationDate>
      <Series>
        <Name>The Wheel of Time</Name>
        <Number>4</Number>
      </Series>
      <Title Chapters="58">The Shadow Rising</Title>
    </Book>
    <Book Type="eBook">
      <Author>Robert Jordan</Author>
      <Id>43</Id>
      <PublicationDate>05/15/1996</PublicationDate>
      <Series>
        <Name>The Wheel of Time</Name>
        <Number>7</Number>
      </Series>
      <Title Chapters="41">A Crown of Swords</Title>
    </Book>
  </Books>
  <Id>51</Id>
  <Name>We Have Books... Read Them or Else!</Name>
</Library>'

쿼리:

SELECT Books.Book.value('(./Id)[1]', 'INT') AS Id,
       Books.Book.value('(./Title)[1]', 'VARCHAR(MAX)') AS Title,
       Books.Book.value('(./Author)[1]', 'VARCHAR(MAX)') AS Author,
       Books.Book.value('(./PublicationDate)[1]', 'DATE') AS PublicationDate,
       Books.Book.value('(./Series/Name)[1]', 'VARCHAR(MAX)') AS SeriesName,
       Books.Book.value('(./Series/Number)[1]', 'VARCHAR(MAX)') AS SeriesNumber,
       Books.Book.value('@Type', 'VARCHAR(MAX)') AS BookType,
       Books.Book.value('(./Title/@Chapters)[1]', 'INT') AS Chapters
FROM @x.nodes('(/Library/Books/Book)') AS Books(Book)

산출량

당신은 매우 쉽게 글을 쓸 수 있습니다. 아래의 세부사항을 찾아주세요.

DECLARE @xmlData 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>'

SELECT 
Col.value('(firstName)[1]','NVARCHAR(50)') AS [FirstName], 
Col.value('(lastName)[1]','NVARCHAR(50)') AS [LastName]
FROM @xmlData.nodes('person') AS tbl(Col)

결과를 보려면 클릭하십시오.

언급URL : https://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008

'code' 카테고리의 다른 글

배시 루프 목록에서 공백을 이스케이프하려면 어떻게 해야 합니까? (0)	2023.04.28
첫 번째, 마지막, 인덱스 루프에 대한 각도 2 ng (0)	2023.04.28
관찰 가능한 컬렉션을 정렬하려면 어떻게 해야 합니까? (0)	2023.04.28
MongoDb를 서비스로 설치한다는 것은 무엇을 의미합니까? (0)	2023.04.28
여러 매개 변수를 ASP에 전달하려면 어떻게 해야 합니까?Net Web API GET? (0)	2023.04.28

현재글SQL Server 2008의 XML 필드에서 값 선택

각종 프로그래밍 정보를 다루는 블로그입니다.

C, angularJS, PYTHON, SWIFT, ReactJS, MariaDB, sql-server, Excel, mysql, wordpress, mongodb, json, spring-boot, asp.net, PowerShell, jQuery, Git, bash, Oracle, ajax,

Today :
Yesterday :

starcafe

SQL Server 2008의 XML 필드에서 값 선택

SQL Server 2008의 XML 필드에서 값 선택

'code' 카테고리의 다른 글

'code'의 다른글

티스토리툴바

« 2025/02 »
일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

SQL Server 2008의 XML 필드에서 값 선택

SQL Server 2008의 XML 필드에서 값 선택

'code' 카테고리의 다른 글

'code'의 다른글

관련글

티스토리툴바