[ms-sql] Xml <--> DataTable 변환 쿼리...

옛날 꼰날... 작업하던중

XML을 통째로 DataTable에 쿼리 하려고 했던적이 있었는데...

잊어버렸다가... 지금 하는 일에 다시 적용해야 될 사항이 생겨서 찾아서 예제로 남김.

어려운거 없다...

-- 예제 결과

-- XML 데이타

<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <INFO>
    <SEQ>1</SEQ>
    <OLDNM>a군</OLDNM>
    <NEWNM />
  </INFO>
  <INFO>
    <SEQ>2</SEQ>
    <OLDNM>b군</OLDNM>
    <NEWNM />
  </INFO>
  <INFO>
    <SEQ>3</SEQ>
    <OLDNM>c군</OLDNM>
    <NEWNM />
  </INFO>
</ROOT>




-- 예제 소스
DECLARE @INFO TABLE
(
    SEQ  VARCHAR(100),
    NM  VARCHAR(100)
)

 

INSERT INTO @INFO VALUES ('1', 'a군' )
INSERT INTO @INFO VALUES ('2', 'b군' )
INSERT INTO @INFO VALUES ('3', 'c군' )

SELECT SEQ, NM as OLDNM, '' [NEWNM] FROM @INFO

DECLARE @XML XML

SET @XML = REPLACE( (SELECT SEQ, NM as OLDNM, '' [NEWNM] FROM @INFO  FOR XML PATH('INFO'), ROOT ('ROOT')) , '<ROOT>' , '<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" >')

SELECT @XML AS [XML]

declare @RegistXML XML
set @RegistXML = '
<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <INFO>
    <SEQ>1</SEQ>
    <OLDNM>a군</OLDNM>
    <NEWNM>x군</NEWNM>
  </INFO>
  <INFO>
    <SEQ>2</SEQ>
    <OLDNM>b군</OLDNM>
    <NEWNM>b군</NEWNM>
  </INFO>
  <INFO>
    <SEQ>3</SEQ>
    <OLDNM>c군</OLDNM>
    <NEWNM></NEWNM>
  </INFO>
</ROOT>'

SELECT
 M.Item.query('./SEQ').value('.','VARCHAR(100)') SEQ,
 M.Item.query('./OLDNM').value('.','VARCHAR(100)') [OLDNM],
 M.Item.query('./NEWNM').value('.','VARCHAR(100)') [NEWNM]
FROM @RegistXML.nodes('/ROOT/INFO') AS M(ITEM)

UPDATE @INFO
SET NM = T.[NEWNM]
FROM  (
  SELECT
   M.Item.query('./SEQ').value('.','VARCHAR(100)') SEQ,
   M.Item.query('./OLDNM').value('.','VARCHAR(100)') [OLDNM],
   M.Item.query('./NEWNM').value('.','VARCHAR(100)') [NEWNM]
  FROM @RegistXML.nodes('/ROOT/INFO') AS M(ITEM)
) T
INNER JOIN @INFO B
ON T.SEQ = B.SEQ and T.[NEWNM] <> ''

SELECT * FROM @INFO

/* XML을TABLE로*/
 
--SELECT * FROM TB_BASE_USER
--WHERE USER_NM LIKE 'KCC%'

반대로 table을 다시 xml로 바꿀때

declare @xxx xml = '<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">' + ( SELECT * FROM @INFO FOR XML PATH ('INFO') ) + '</ROOT>'
 
select @xxx