XML 문서 쿼리로 조회하기

 

XML 텍스트를 sql에서 조회시

샘플 XML(샘플에서는 TableSample가 한개이지만, 여러개가 와도 된다.)

 

기본형(복불 후 결과 볼수 있음)

기본형으로 단순 XML문서를 읽어들여서 출력함.

<DataTable>
 <TableSample>
  <a>01808</a>
  <b>D</b>
  <c>00373</c>
  <d>2015-05-01T00:00:00+09:00</d>
  <e>00D</e>
  <f>2</f>
  <g>000</g>
 </TableSample>
</DataTable>

DECLARE @XML    XML 

 

SET @XML=convert(xml,N'<DataTable><TableSample><a>01808</a><b>D</b><c>00373</c><d>2015-05-01T00:00:00+09:00</d><e>00D</e><f>2</f><g>000</g></TableSample></DataTable>')

DECLARE @IDOC INT 

EXEC sp_xml_preparedocument @DOC OUTPUT, @XML 

 

SELECT * 
FROM OPENXML(@DOC, '/DataTable/TableSample', 2) 
WITH (
      a varchar(500) 'a'
    , b varchar(500) 'b' 
    , c varchar(500) 'c'
    , d varchar(500) 'd'
    , e varchar(500) 'e'
    , f varchar(500) 'f'
    , g varchar(500) 'g'
)

*복불 후 결과 볼수 있음

 

 

응용형 1

xml문서에 하위 노드, 속성등이 있고 그 정보중 원하는 데이터만 조회하고 싶을 때

 

불러오고 싶은 데이터에 따라 다음과 같이 사용.
속성 : '@aa'
요소 : 'bb'
하위 속성'./cc/@aa'
하위 요소'./cc/aa'

 

 

<DataTable>
 <TableSample>
  <a test='testvalue'>01808</a>
  <b>D</b>
  <c>00373</c>
  <d>2015-05-01T00:00:00+09:00</d>
  <e>00D</e>
  <f>2</f>
  <g>
   <ga>ga입니다.</ga>
   <gb gbTest="gb value">gb입니다.</gb>
  </g>  
 </TableSample>
</DataTable>

DECLARE @XML    XML 

 

SET @XML=convert(xml,N'<DataTable><TableSample><a test="testvalue">01808</a><b>D</b><c>00373</c><d>2015-05-01T00:00:00+09:00</d><e>00D</e><f>2</f><g><ga>ga입니다.</ga><gb gbTest="gb value">gb입니다.</gb></g></TableSample></DataTable>')

 

DECLARE @DOC INT 

EXEC sp_xml_preparedocument @DOC OUTPUT, @XML 

 

SELECT * 
FROM OPENXML(@DOC, '/DataTable/TableSample', 2) 
WITH (
      a varchar(500) 'a'
    , b varchar(500) 'b' 
    , c varchar(500) 'c'
    , d varchar(500) 'd'
    , e varchar(500) 'e'
    , f varchar(500) 'f'
    , g varchar(500) './g/ga'
    , h varchar(500) './g/gb/@gbTest'
)

*복불 후 결과 볼수 있음

 

 

 

 

응용형 2

XML text의 스키마와 Database의 table 스키마와 동일 할경우, With [테이블 이름]을 사용하여 출력할 수 있다.

 

DECLARE @XML    XML 

 

SET @XML=convert(xml,N'<DataTable><TableSample><a>01808</a><b>D</b><c>00373</c><d>2015-05-01T00:00:00+09:00</d><e>00D</e><f>2</f><g>000</g></TableSample></DataTable>')

 

DECLARE @DOC INT 

EXEC sp_xml_preparedocument @DOC OUTPUT, @XML 

 

SELECT XML_TABLE.* 
FROM OPENXML(@DOC, '/DataTable/TableSample', 2) 
WITH TableSample XML_TABLE

 

*복불 후 결과 볼수 없음. ㅜㅜ