Insert customized bulk data into sql datatable.

Hi All,

What I am trying to achieve here is, I am supplying xml as string (varchar) to my stored procedure, In stored procedure I want to parse this xml and fetch each Promotion record and insert into my table. Also once each record is inserted, I need to fetch the Identity of that record alongwith the AreaID attribute from the Parent node "Promotion" of each Promotion record and insert into second association table. Please guide how would I go about doing it, my generated xml is given as under.

<PromoData>
<Promotion AreaID="1">
<PromoName>Rest & Relaxation Sale</PromoName>
<PromoDesc>Rest & Relaxation Sale</PromoDesc><PromoCode>SYSPROM197</PromoCode>
<BeginDate>01/21/2010</BeginDate>
<EndDate>01/23/2010</EndDate>
<RawHtml><h4>Hi</h4></RawHtml>
<FocusPromoInd>1</FocusPromoInd>
<FocusPromoCode></FocusPromoCode>
<ActiveInd>1</ActiveInd>
</Promotion>
<Promotion AreaID="2">
<PromoName>Rest & Relaxation Sale</PromoName>
<PromoDesc></PromoDesc>
<PromoCode></PromoCode>
<BeginDate>01/21/2010</BeginDate>
<EndDate>01/23/2010</EndDate>
<RawHtml><h4>Hi</h4></RawHtml>
<FocusPromoInd>0</FocusPromoInd>
<FocusPromoCode>SYSPROM197</FocusPromoCode>
<ActiveInd>1</ActiveInd>
</Promotion>
<Promotion AreaID="3">
<PromoName>Rest & Relaxation Sale</PromoName>
<PromoDesc></PromoDesc>
<PromoCode></PromoCode>
<BeginDate>01/21/2010</BeginDate>
<EndDate>01/23/2010</EndDate>
<RawHtml><h4>Hi</h4></RawHtml>
<FocusPromoInd>0</FocusPromoInd>
<FocusPromoCode>SYSPROM197</FocusPromoCode>
<ActiveInd>1</ActiveInd>
</Promotion>
</PromoData>

I would appreciate any help, Thanks!

Re: Insert customized bulk data into sql datatable.

Hi,

You can use OpenXml. Please refer following

http://msdn.microsoft.com/en-us/library/aa276847%28SQL.80%29.aspx

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

http://weblogs.asp.net/guys/archive/2004/12/21/328214.aspx

http://forums.asp.net/t/1525817.aspx

hope this helps

Re: Insert customized bulk data into sql datatable.

Hi Ketan,

Thanks for the articles, that helped me volumes writing the whole procedure from scratch. I have been able to fetch and read xml and insert data into my table. The only problem I seem to be facing is, how what modification should I make to read the AreaID which is passed as an attribute of promotion anchor. I have tried passing flags 0/8 but to no avail. 2 works fins as far as I am reading elements.

Here is the chuck of my sproc.

DECLARE @XmlDocID int,
			@RecordCount int,
			@CurrentRecord int,
			@IsDebug bit
	DECLARE @Promotion TABLE (PromoID int NOT NULL IDENTITY (1,1),
									 PromoName nvarchar(max) NOT NULL,
									 PromoDesc nvarchar(max) NULL,
									 PromoCode varchar(10)  NOT NULL,
									 BeginDate datetime NOT NULL,
									 EndDate datetime NOT NULL,
									 CreatedDate datetime NOT NULL,
									 RawHtml nvarchar(max) NOT NULL,
									 FocusPromoInd bit NOT NULL,
									 FocusPromoCode varchar(10) NULL,
									 ActiveInd bit NOT NULL,
									 AreaID int NOT NULL
									)
									

	SET @IsDebug = 1
	set @XmlPromoDoc = '
	<PromoData>
	<Promotion AreaID="1">
		<PromoName>Friends & Family Event</PromoName>
		<PromoDesc>Friends & Family Event</PromoDesc>
		<PromoCode>SYSPROM217</PromoCode>
		<BeginDate>05/01/2010</BeginDate>
		<EndDate>05/02/2010</EndDate>
		<RawHtml><h4>Hi</h4></RawHtml>
		<FocusPromoInd>1</FocusPromoInd>
		<FocusPromoCode></FocusPromoCode>
		<ActiveInd>1</ActiveInd>
	</Promotion><Promotion AreaID="2">
		<PromoName>Friends & Family Event</PromoName>
		<PromoDesc></PromoDesc>
		<PromoCode></PromoCode>
		<BeginDate>05/01/2010</BeginDate>
		<EndDate>05/02/2010</EndDate>
		<RawHtml><h4>Hi</h4></RawHtml>
		<FocusPromoInd>0</FocusPromoInd>
		<FocusPromoCode>SYSPROM217</FocusPromoCode>
		<ActiveInd>1</ActiveInd>
	</Promotion>
	<Promotion AreaID="3">
		<PromoName>Friends & Family Event</PromoName>
		<PromoDesc></PromoDesc>
		<PromoCode></PromoCode>
		<BeginDate>05/01/2010</BeginDate>
		<EndDate>05/02/2010</EndDate>
		<RawHtml><h4>Hi</h4></RawHtml>
		<FocusPromoInd>0</FocusPromoInd>
		<FocusPromoCode>SYSPROM217</FocusPromoCode>
		<ActiveInd>1</ActiveInd>
	</Promotion>
</PromoData>'


	BEGIN TRANSACTION Tran_XmlSubmit


	BEGIN TRY
		EXEC sp_xml_preparedocument @XmlDocID OUTPUT, @XmlPromoDoc
		INSERT @Promotion (PromoName, PromoDesc, PromoCode, BeginDate,
							EndDate, CreatedDate, RawHtml, FocusPromoInd,
							FocusPromoCode, ActiveInd, AreaID)
		SELECT  LTRIM(RTRIM(PromoName)) AS PromoName,
				CASE WHEN Len(IsNull(PromoDesc,'')) = 0 THEN NULL ELSE LTRIM(RTRIM(PromoDesc)) END AS PromoDesc,
				LTRIM(RTRIM(PromoCode)) AS PromoCode,
				LTRIM(RTRIM(BeginDate))AS BeginDate,
				LTRIM(RTRIM(EndDate))AS EndDate,
				GETDATE() AS CreateDate,
				LTRIM(RTRIM(RawHtml)) AS RawHtml,
				CONVERT(Bit, FocusPromoInd) AS FocusPromoInd,
				LTRIM(RTRIM(FocusPromoCode))AS FocusPromoCode,
				CONVERT(Bit, ActiveInd) AS ActiveInd,
				CONVERT(INT, LTRIM(RTRIM(AreaID))) AS AreaID
		FROM OPENXML (@XmlDocID, '/PromoData/Promotion', 2)
				WITH (PromoName nvarchar(max),
					  PromoDesc nvarchar(max),
					  PromoCode varchar(10),
					  BeginDate datetime,
					  EndDate datetime,
					  CreatedDate datetime,
					  RawHtml nvarchar(max),
					  FocusPromoInd bit,
					  FocusPromoCode varchar(10),
					  ActiveInd bit,
					  AreaID varchar(3)
					 )
		--Done parsing XML. Close handle.
		EXEC sp_xml_removedocument @XmlDocID
		COMMIT TRANSACTION Tran_XmlSubmit

It reads the xml, but when I insert read xml to @Promotion it returns error and rolls back transaction. Please help!