데이터베이스/Mssql

xml 읽어서 insert 하기 - MSSQL

Jinwookoh 2014. 8. 18. 23:42

INSERT INTO [dbo].[tbJinwook_VendorSeq_Temp]

           ([AppDocSeq]
           ,[vSeq]
           ,[vCode]
           ,[vName]
           ,[dDate]
           ,[eType]
           ,[Vat]
           ,[Amt]
           ,[tAmt])
SELECT 
AppDocSeq, 
'' AS vSeq,
isnull(CONVERT(XML, Content).value('(ExpItem[4]/vCode[1])', 'varchar(max)'),'') AS vCode, 
isnull(CONVERT(XML, Content).value('(ExpItem[4]/vName[1])', 'varchar(max)'),'') AS vName,
isnull(CONVERT(XML, Content).value('(ExpItem[4]/dDate[1])', 'varchar(max)'),'') AS dDate,
isnull(CONVERT(XML, Content).value('(ExpItem[4]/eType[1])', 'varchar(max)'),'') AS eType,
isnull(CONVERT(XML, Content).value('(ExpItem[4]/Vat[1])', 'varchar(max)'),'') AS Vat,
isnull(CONVERT(XML, Content).value('(ExpItem[4]/Amt[1])', 'varchar(max)'),'') AS Amt,
isnull(CONVERT(XML, Content).value('(ExpItem[4]/tAmt[1])', 'varchar(max)'),'') AS tAmt
FROM tbForm00021
where CONVERT(XML, Content).value('(ExpItem[4]/vCode[1])', 'varchar(max)') is not null
and AppDocSeq in (select AppDocSeq from tbAppDoc Where FormSeq in (14,21,10) AND DraftDT > '2013-01-01')
Order by AppDocSeq ASC