SQL-Server sp 에서 xml 처리

DataBase/SQL Server

by 탑~! 2011. 3. 24. 11:55

declare @xmlData xml

set @xmlData = '<NewDataSet>
    <FixOrCancelList>
   <Vessel>06100</Vessel>
   <Voy>0001</Voy>
   <FixYN>Y</FixYN>
    </FixOrCancelList>
    <FixOrCancelList>
   <Vessel>06100</Vessel>
   <Voy>0002</Voy>
   <FixYN>Y</FixYN>
    </FixOrCancelList>
    <FixOrCancelList>
   <Vessel>06100</Vessel>
   <Voy>0003</Voy>
   <FixYN>Y</FixYN>
    </FixOrCancelList>
    <FixOrCancelList>
   <Vessel>06166</Vessel>
   <Voy>0001</Voy>
   <FixYN>Y</FixYN>
    </FixOrCancelList>
    <FixOrCancelList>
   <Vessel>06166</Vessel>
   <Voy>0002</Voy>
   <FixYN>Y</FixYN>
    </FixOrCancelList>
    <FixOrCancelList>
   <Vessel>06166</Vessel>
   <Voy>0003</Voy>
   <FixYN>Y</FixYN>
    </FixOrCancelList>
    <FixOrCancelList>
   <Vessel>06166</Vessel>
   <Voy>0004</Voy>
   <FixYN>Y</FixYN>
    </FixOrCancelList>
  </NewDataSet>'

SELECT ItemData.Row.value('Vessel[1]', 'varchar(5)') Vessel
  ,ItemData.Row.value('Voy[1]', 'varchar(4)') Voy
  ,ItemData.Row.value('FixYN[1]', 'varchar(4)') FixYN
FROM @xmlData.nodes('/NewDataSet/FixOrCancelList') ItemData(row)

저작자표시

'DataBase > SQL Server' 카테고리의 다른 글

RAISERROR 예제 및 .NET에서 사용하기 (0)	2011.09.01
TRY ~ CATCH (0)	2011.05.26
SP 에서 특정문자열을 기준으로 데이타를 테이블로 반환한다. (0)	2010.12.02
SQLServer : Convert/Cast 의 새로운 발견 (0)	2010.11.26
SQL Server 2008 T-SQL (0)	2010.11.18