MSSQL Query to XML- dtd를 포함하는 xml 추출

※쿼리에 관한 글 입니다. 최적화로 보시기 위해, pc버전으로 보시는것을 권장 드립니다.

mssql을 이용하여 xml 형태의 문서를 만든다. 여러가지 방법이 있지만, FOR XML EXPLICIT 형태를 이용하는것이, cdata를 만들고 컬럼을 지정해주기 가장 간편한듯하여 사용한다. 대상db는 mssql master db에 있는 테이블을 활용했고, 그대로 복사 붙여넣기 하면 사용할 수 있다.

프로그램에서 sql을 상단, 본문, 하단의 결과값을 stream을 이용하여 파일에 작성 후 .xml로 저장하면, 원하는 형태의 파일을 볼수있다.

1) xml 상단부

select '<?xml version="1.0" encoding="UTF-8"?><www.test.kr><result><![CDATA[ OK ]]>

</result><itemCount><![CDATA[ '

+ convert(varchar,count(*)) + ' ]]></itemCount><itemList>' as col1

2) 원본 데이터 (배열형태로 생성)

select 1 as 'TAG' 
 ,NULL as Parent
 ,lastrun as [item!1!lastrun!cdata]
 ,cpu_busy as [item!1!cpu_busy!cdata]
 ,io_busy as [item!1!io_busy!cdata]
 ,idle as [item!1!idle!cdata]
 ,pack_received as [item!1!pack_received!cdata]
 ,pack_sent as [item!1!pack_sent!cdata]
 ,connections as [item!1!connections!cdata]
 ,pack_errors as [item!1!pack_errors!cdata]
 ,connections as [item!1!connections!cdata]
 ,pack_errors as [item!1!pack_errors!cdata]
from master.[dbo].[spt_monitor]
FOR XML EXPLICIT

3) xml 하단부

select '</itemList></www.test.kr>' as col3

※데이터 생성 샘플

<item>
  <lastrun><![CDATA[2014-02-20T20:49:38.300]]></lastrun>
  <cpu_busy><![CDATA[7]]></cpu_busy>
  <io_busy><![CDATA[2]]></io_busy>
  <idle><![CDATA[487]]></idle>
  <pack_received><![CDATA[39]]></pack_received>
  <pack_sent><![CDATA[39]]></pack_sent>
  <connections><![CDATA[22]]></connections>
  <pack_errors><![CDATA[0]]></pack_errors>
  <connections><![CDATA[22]]></connections>
  <pack_errors><![CDATA[0]]></pack_errors>
</item>