---
tags: MSSQL T-SQL
description:
---

Parsing XML file using T-SQL
===

#### [SpInsert_BEFORExml]

Begin
	set nocount on
	declare @Xml XML, @hDoc AS INT
	declare @sql nvarchar(256);
	declare @DeviceID as varchar(50)
	declare @CollectionTime as varchar(30)
	declare @SubstrateId as varchar(50)
	declare @LotId as varchar(50)
	declare @GoodDevices as int
	declare @SupplierName as varchar(50)
	declare @Delete as smallint
	declare @xmlFile  as varchar(255)
	
	--測試的xml檔
	set @xmlFile = N'D:\Thinning\B2B\VIS\Before\2019\201910\GBW384.1-E142.XML'

	----------bulkcolumn------------
	--使用bulkcolumn讀取大檔案
	--語法：
	--SELECT BulkColumn FROM OPENROWSET (BULK 'c:\temp\mytxtfile.txt', SINGLE_CLOB) MyFile
	--參數 
	--SINGLE_BLOB，用二進位讀取一個文件(最大值)
	--SINGLE_CLOB，用varchar讀取一個文件(最大值)
	--SINGLE_NCLOB，用nvarchar讀取一個文件(最大值)
	
	-----------sp_executesql--------
	--使用sp_executesql動態組合sql
	--語法
	/*
	sp_executesql [ @stmt = ] statement  
    [   
      { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
         { , [ @param1 = ] 'value1' [ ,...n ] }  
    ] 
    */
	set @sql='SELECT @Xml=BulkColumn FROM OPENROWSET(BULK '''+@xmlFile+''', SINGLE_BLOB) AS T'
	exec sp_executesql @stmt=@sql, @param=N'@Xml XML OUT', @Xml=@Xml OUT
	select @Xml;

	--DeviceID
	with XMLNAMESPACES(default 'urn:semi-org:xsd.E142-1.V1005.SubstrateMap')
	select Top 1 @DeviceID=C.value('@LayoutId','varchar(50)')
		from @Xml.nodes('/MapData/Layouts/Layout') as XTAB(C);

	--BEFOREhead/BEFOREbin/BEFOREmapRow
	declare CursorXML cursor for 
		with XMLNAMESPACES(default 'urn:semi-org:xsd.E142-1.V1005.SubstrateMap')
			select SubstrateId = C.value('@SubstrateId','varchar(50)'),
				   LotId = C.value('LotId[1]','varchar(50)'),
				   GoodDevices = C.value('GoodDevices[1]','int'),
				   SupplierName	= C.value('SupplierName[1]','varchar(50)')
				from @Xml.nodes('/MapData/Substrates/Substrate') as XTAB(C);

	--set @Delete = 1
	open CursorXML
		fetch next from CursorXML into @SubstrateId, @LotId, @GoodDevices, @SupplierName
		while @@FETCH_STATUS = 0 begin
			-- delete BEFOREhead
			--if @Delete=1 begin
			--	set @Delete = 0
			--	delete from [CIMDB].[dbo].[BEFOREhead] where LotNo=@LotId
				--delete from [CIMDB].[dbo].[BEFOREmapRowVIS] where LotNo=@LotId
			--	end;

			-- BEFOREhead
			delete from [CIMDB].[dbo].[BEFOREhead]
				where LotNo=@LotId
				  and WaferID=@SubstrateId;  --CollectionTime=@CollectionTime
			with XMLNAMESPACES(default 'urn:semi-org:xsd.E142-1.V1005.SubstrateMap')
			select top 1 @CollectionTime=C.value('../../../@MapVersion','varchar(30)')
				from @Xml.nodes('/MapData/SubstrateMaps/SubstrateMap/Overlay/BinCodeMap/BinDefinitions/BinDefinition') as XTAB(C)
				where C.value('../../../../@SubstrateId','varchar(50)')=@SubstrateId;
			--with XMLNAMESPACES(default 'urn:semi-org:xsd.E142-1.V1005.SubstrateMap')
			--select top 1 @DeviceID=left(C.value('../../../@LayoutSpecifier','varchar(50)'), charindex('/', C.value('../../../@LayoutSpecifier','varchar(50)'))-1)	--left(C.value('../../../@LayoutSpecifier','varchar(50)'), 6)	
			--	from @Xml.nodes('/MapData/SubstrateMaps/SubstrateMap') as XTAB(C)
			--	where C.value('../../../../@SubstrateId','varchar(50)')=@SubstrateId;
			insert into [CIMDB].[dbo].[BEFOREhead](CollectionTime,  LotNo,  WaferID,      DeviceID,  GoodDies,     SupplierName,  xmlFile)
											values(@CollectionTime, @LotId, @SubstrateId, @DeviceID, @GoodDevices, @SupplierName, @xmlFile);
			
			-- BEFOREbin
			with XMLNAMESPACES(default 'urn:semi-org:xsd.E142-1.V1005.SubstrateMap')
			insert into [CIMDB].[dbo].[BEFOREbin]
				select @CollectionTime,		--Overlay_MapVersion	= C.value('../../../@MapVersion','datetime')
					   @LotId,
					   @SubstrateId, 
					   C.value('@BinCode','char(3)'),
					   C.value('@BinDescription','varchar(32)'),
					   C.value('@BinCount','int'),
					   case when Upper(C.value('@BinQuality','varchar(32)'))='PASS' then 1 else 0 end,
					   C.value('@Pick','bit')
					from @Xml.nodes('/MapData/SubstrateMaps/SubstrateMap/Overlay/BinCodeMap/BinDefinitions/BinDefinition') as XTAB(C)
					where C.value('../../../../@SubstrateId','varchar(50)')=@SubstrateId;
			
			-- BEFOREmapRow
			with XMLNAMESPACES(default 'urn:semi-org:xsd.E142-1.V1005.SubstrateMap')
			insert into [CIMDB].[dbo].[BEFOREmapRow]
				select @CollectionTime, 
					   @LotId, 
					   @SubstrateId, 
					   ROW_NUMBER() over (Partition by SubstrateId order by SubstrateId, RowsX desc), 
					   BinCode
					from (select SubstrateId = C.value('../../../@SubstrateId','varchar(50)'),
								 RowsX=ROW_NUMBER() over (order by (select 1000)),
								 BinCode = C.value('.','varchar(max)')
								from @Xml.nodes('/MapData/SubstrateMaps/SubstrateMap/Overlay/BinCodeMap/BinCode') as XTAB(C)
						 ) A
					where A.SubstrateId=@SubstrateId
	
			fetch next from CursorXML into @SubstrateId, @LotId, @GoodDevices, @SupplierName
		end
	close CursorXML
	deallocate CursorXML;

	/*
	-- BEFOREmapRowVIS
	with XMLNAMESPACES(default 'urn:semi-org:xsd.E142-1.V1005.SubstrateMap')
	--delete from [CIMDB].[dbo].[BEFOREmapRowVIS] where
	insert into [CIMDB].[dbo].[BEFOREmapRowVIS]
		select @LotId,
			   SubstrateId,
			   ROW_NUMBER() over (Partition by SubstrateId order by SubstrateId, RowsX desc),
			   CollectionTime,
			   BinCode
			from (select SubstrateId = C.value('../../../@SubstrateId','varchar(50)'),
						 RowsX=ROW_NUMBER() over (order by (select 1000)),
						 CollectionTime = C.value('../../@MapVersion','varchar(30)'),
						 BinCode = C.value('.','varchar(max)')
					from @Xml.nodes('/MapData/SubstrateMaps/SubstrateMap/Overlay/BinCodeMap/BinCode') as XTAB(C)
				 ) A
	*/

End