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.
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!
Ashar A. Syed.
[Like To Help Me. Like My Post & Mark As Answered If It Helped You]
asharsyed
Member
71 Points
123 Posts
Insert customized bulk data into sql datatable.
Jul 29, 2010 03:20 PM|LINK
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.
I would appreciate any help, Thanks!
[Like To Help Me. Like My Post & Mark As Answered If It Helped You]
ketan_al
Contributor
6850 Points
1143 Posts
Re: Insert customized bulk data into sql datatable.
Jul 29, 2010 04:29 PM|LINK
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
MCP, MCTS ( ASP.NET 3.5 )
Please mark as answer if it helps :)
asharsyed
Member
71 Points
123 Posts
Re: Insert customized bulk data into sql datatable.
Jul 30, 2010 12:29 PM|LINK
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.
It reads the xml, but when I insert read xml to @Promotion it returns error and rolls back transaction. Please help!
[Like To Help Me. Like My Post & Mark As Answered If It Helped You]