Problems with parsing large XML data

Good day.
I'm trying to import an large XML data file (about 10M) in several tables in
MSSQL database.
There are a couple of entities in this file, one entity with large amount of
records (about 50000), other with less than 2000 records. There were no
problems with small recordsets, i've parsed and imported them using iteratio
n
through cursors like:
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
with (phonenumber nvarchar(20) '../../phonenumber',
catarget nvarchar(256) 'catarget',
caquant nvarchar(256) 'caquant',
casum money 'casum'
)
But i couldn't import large recordsets, SQL server just silently rollbacks
transaction, it even doesn't throw any errors.
I've tried cursors (several combinations of static, dynamic, locak and
global modifyers) and INSERT FROM SELECT methods of parsing. When i
experimented used cursors, maximum row numbers in file before fault was abou
t
5000, with INSERT FROM SELECT (into temp. table) maximum rows number before
fault was like 1500 record. Approx. record size is near 1 k.
Any suggestions on this problem?UPDATE:
Having had run the same SP on my 512 RAM notebook instead of 256RAM PC, i
didn't come across this fault.|||It should not throw no errors, so either you have an older version or you
found a n interesting problem.
What version of SQL Server 2000 are you running (what service pack?) and how
did you install it (upgrade or fresh)?
Also, I would recommend that you copy the information into a table (could be
a temp table) from OpenXML and then do the cursor over that table (and why
do you need cursors in the first place?).
Best regards
Michael
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:D58AF7A2-1543-4EC8-8AA7-8AE763BCDD3D@.microsoft.com...
> Good day.
> I'm trying to import an large XML data file (about 10M) in several tables
> in
> MSSQL database.
> There are a couple of entities in this file, one entity with large amount
> of
> records (about 50000), other with less than 2000 records. There were no
> problems with small recordsets, i've parsed and imported them using
> iteration
> through cursors like:
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> catarget nvarchar(256) 'catarget',
> caquant nvarchar(256) 'caquant',
> casum money 'casum'
> )
> But i couldn't import large recordsets, SQL server just silently rollbacks
> transaction, it even doesn't throw any errors.
> I've tried cursors (several combinations of static, dynamic, locak and
> global modifyers) and INSERT FROM SELECT methods of parsing. When i
> experimented used cursors, maximum row numbers in file before fault was
> about
> 5000, with INSERT FROM SELECT (into temp. table) maximum rows number
> before
> fault was like 1500 record. Approx. record size is near 1 k.
> Any suggestions on this problem?|||"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23XREgotDFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Also, I would recommend that you copy the information into a table (could
be
> a temp table) from OpenXML and then do the cursor over that table (and why
> do you need cursors in the first place?).
I was going to suggest that the OP lose the cursor altogether until I
noticed:
So apparently, the OP had a non-cursor solution to begin with...?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"Michael Rys [MSFT]" wrote:

> It should not throw no errors, so either you have an older version or you
> found a n interesting problem.
> What version of SQL Server 2000 are you running (what service pack?) and h
ow
> did you install it (upgrade or fresh)?
version with fault is MSDE
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
fresh installation, withous any service packs

> Also, I would recommend that you copy the information into a table (could
be
> a temp table) from OpenXML and then do the cursor over that table (and why
> do you need cursors in the first place?).
>
I use cursors because each record in XML file has to be splitted between a
couple of tables, some of them have identity surrogate PK's which of course
aren't supplied in XML file. In short, this XML doesn't represents the
database design. So I couldn't just bulk insert records in tables.
Anyway, i've tried ti use temporary tables, but without much beneficial
effects.
Only running on other server that has more RAM helps.
Below is the script of SP. It fails on last OPENXML. It contsains some
cyrillic characters, so probably you couldn't read error messages and
comments. Also it uses other SP's
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc parseXML
@.XMLData ntext
as
BEGIN
BEGIN TRAN
SET DATEFORMAT dmy
declare @.phone_id int
declare @.lastphonenum nvarchar(20)
DECLARE @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XMLData
--CALL PERIOD
declare @.callperiod datetime
declare @.callperiod_id int
select @.callperiod = callperiod from OPENXML (@.hdoc, '/bill', 2) with
(callperiod nvarchar(20))
IF ( @.@.ROWCOUNT > 0)
BEGIN
EXEC sp_addcallperiod @.callperiod, @.callperiod_id OUTPUT
END
ELSE BEGIN
EXEC sp_xml_removedocument @.hdoc
RAISERROR ('Не найден элемент callperiod', 16, 1)
ROLLBACK TRAN
return -1
END
-- номера телефонов и summary
declare @.phonenum varchar(20)
declare @.phonesummary money
declare @.summary_id int
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone', 2)
with (phonenumber nvarchar(20) './phonenumber',
phonesummary money './summary'
)
OPEN phonecursor
FETCH NEXT FROM phonecursor INTO @.phonenum, @.phonesummary
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--добавляем номер телефона
exec sp_addphone @.phonenum, @.phone_id OUTPUT
--добавляем summary
exec sp_addphonesummary @.phonesummary, @.phone_id, @.callperiod_id,
@.summary_id OUTPUT
FETCH NEXT FROM phonecursor INTO @.phonenum, @.phonesummary
END
CLOSE phonecursor
DEALLOCATE phonecursor
--operaggr
declare @.oaname nvarchar(128)
declare @.oasum money
declare @.aggr_id int
select @.lastphonenum = ''
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone/operaggrs/operaggr', 2)
with (phonenumber nvarchar(20) '../../phonenumber',
oaname nvarchar(128) 'oaname',
oasum money 'oasum'
)
OPEN phonecursor
FETCH NEXT FROM phonecursor INTO @.phonenum, @.oaname, @.oasum
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--получаем ID номера телефона
IF @.lastphonenum <> @.phonenum
BEGIN
exec sp_addphone @.phonenum, @.phone_id OUTPUT
select @.lastphonenum = @.phonenum
END
--добавляем агрегаты
exec sp_addphoneoperationaggr @.oasum, @.phone_id, @.callperiod_id,
@.oaname, @.aggr_id OUTPUT
FETCH NEXT FROM phonecursor INTO @.phonenum, @.oaname, @.oasum
END
CLOSE phonecursor
DEALLOCATE phonecursor
--callaggr
declare @.catarget nvarchar(256)
declare @.caquant nvarchar(20)
declare @.casum money
declare @.caggr_id int
select @.lastphonenum = ''
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
with (phonenumber nvarchar(20) '../../phonenumber',
catarget nvarchar(256) 'catarget',
caquant nvarchar(256) 'caquant',
casum money 'casum'
)
OPEN phonecursor
FETCH NEXT FROM phonecursor INTO @.phonenum, @.catarget, @.caquant, @.casum
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--получаем ID номера телефона
IF @.lastphonenum <> @.phonenum
BEGIN
exec sp_addphone @.phonenum, @.phone_id OUTPUT
select @.lastphonenum = @.phonenum
END
--добавляем агрегаты
exec sp_addphonecallaggr @.catarget, @.caquant, @.casum, @.phone_id,
@.callperiod_id, @.caggr_id OUTPUT
FETCH NEXT FROM phonecursor INTO @.phonenum, @.catarget, @.caquant, @.casum
END
CLOSE phonecursor
DEALLOCATE phonecursor
--звонки
--THIS SECTION FAILS!!!!
declare @.i int
select @.i = 0
declare @.call_id int
declare @.price money
declare @.pszone nvarchar(128)
declare @.vtkzone nvarchar(128)
declare @.ctchar char(1)
declare @.ctype nvarchar(20)
declare @.amount nvarchar(20)
declare @.direct int
declare @.ctime nvarchar(30)
declare @.dt datetime
select @.lastphonenum = ''
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone/calls/call', 2)
with (phonenumber nvarchar(20) '../../phonenumber',
price money 'price',
pszone nvarchar(128) 'pszone',
vtkzone nvarchar(128) 'vtkzone',
ctchar char(1) 'ctchar',
ctype nvarchar(20) 'ctype',
amount nvarchar(20) 'amount',
direct int 'direct',
ctime nvarchar(30) 'ctime'
)
OPEN phonecursor
FETCH NEXT FROM phonecursor INTO @.phonenum, @.price, @.pszone, @.vtkzone,
@.ctchar, @.ctype, @.amount, @.direct, @.ctime
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
select @.dt = cast(@.ctime as datetime)
--получаем ID номера телефона
IF @.lastphonenum <> @.phonenum
BEGIN
exec sp_addphone @.phonenum, @.phone_id OUTPUT
select @.lastphonenum = @.phonenum
END
exec sp_addcall @.dt, @.amount, @.price, @.pszone, @.vtkzone, @.ctype, @.ctchar,
@.phone_id, @.direct, @.callperiod_id, @.call_id OUTPUT
FETCH NEXT FROM phonecursor INTO @.phonenum, @.price, @.pszone, @.vtkzone,
@.ctchar, @.ctype, @.amount, @.direct, @.ctime
end;
CLOSE phonecursor
DEALLOCATE phonecursor
EXEC sp_xml_removedocument @.hdoc
COMMIT TRAN
return 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Structure of XML file is like
<?xml version="1.0"?>
<bill>
<callperiod>30.11.2004</callperiod>
<phones>
<phone>
<phonenumber>0923061267</phonenumber>
<summary>241.9676</summary>
<operaggrs>
<operaggr>
<oaname>WAP (старая)</oaname>
<oasum>1.8311</oasum>
</operaggr>
<operaggr>
<oaname>Абонентская плата/ Обязательная
ежемесячная
плата за трафик</oaname>
<oasum>5.5117</oasum>
</operaggr>
</operaggrs>
<callaggrs>
<callaggr>
<catarget>SMS исходящее: 12 категория</catarget>
<caquant>1 факт</caquant>
<casum>0.75</casum>
</callaggr>
<callaggr>
<catarget>SMS входящее</catarget>
<caquant>4 факт</caquant>
<casum>0</casum>
</callaggr>
</callaggrs>
<calls>
<call>
<price>0</price>
<pszone></pszone>
<vtkzone></vtkzone>
<ctchar></ctchar>
<ctype>Телеф.</ctype>
<amount>0: 01</amount>
<direct>0</direct>
<ctime>12.11.2004 15:15:00</ctime>
</call>
<call>
<price>0</price>
<pszone></pszone>
<vtkzone></vtkzone>
<ctchar></ctchar>
<ctype>Телеф.</ctype>
<amount>0: 01</amount>
<direct>1</direct>
<ctime>12.11.2004 15:15:00</ctime>
</call>
</calls>
</phone>
<phone>
.......
</phone>
..........
</phones>
</bill>
there are about 120 <phone> nodes and about 50000 total <call> nodes in it|||Have you thought about writing a transform to generate a script of insert
statements?
Then just run in the script through query analyser.
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:D58AF7A2-1543-4EC8-8AA7-8AE763BCDD3D@.microsoft.com...
> Good day.
> I'm trying to import an large XML data file (about 10M) in several tables
> in
> MSSQL database.
> There are a couple of entities in this file, one entity with large amount
> of
> records (about 50000), other with less than 2000 records. There were no
> problems with small recordsets, i've parsed and imported them using
> iteration
> through cursors like:
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> catarget nvarchar(256) 'catarget',
> caquant nvarchar(256) 'caquant',
> casum money 'casum'
> )
> But i couldn't import large recordsets, SQL server just silently rollbacks
> transaction, it even doesn't throw any errors.
> I've tried cursors (several combinations of static, dynamic, locak and
> global modifyers) and INSERT FROM SELECT methods of parsing. When i
> experimented used cursors, maximum row numbers in file before fault was
> about
> 5000, with INSERT FROM SELECT (into temp. table) maximum rows number
> before
> fault was like 1500 record. Approx. record size is near 1 k.
> Any suggestions on this problem?|||
"David McNally" wrote:

> Have you thought about writing a transform to generate a script of insert
> statements?
> Then just run in the script through query analyser.
Unfortunatelly, it is against requirements specification. As a part of lare
automatization project, it should be a SP which processes XML data stream.
Application is to supply this stream via ADO.|||Thanks for the info. I will have this looked at by our team. Note that this
looks like the RTM version of SQL Server which is vulnerable to the Slammer
virus. So you may want to try it with MSDE SP3a (and download the MSXML 2.6
SP3).
Best regards
Michael
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:28438F56-430E-45F1-AFB3-AD05548ADA24@.microsoft.com...
> "Michael Rys [MSFT]" wrote:
>
> version with fault is MSDE
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
> fresh installation, withous any service packs
>
> I use cursors because each record in XML file has to be splitted between a
> couple of tables, some of them have identity surrogate PK's which of
> course
> aren't supplied in XML file. In short, this XML doesn't represents the
> database design. So I couldn't just bulk insert records in tables.
> Anyway, i've tried ti use temporary tables, but without much beneficial
> effects.
> Only running on other server that has more RAM helps.
>
> Below is the script of SP. It fails on last OPENXML. It contsains some
> cyrillic characters, so probably you couldn't read error messages and
> comments. Also it uses other SP's
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
>
> CREATE proc parseXML
> @.XMLData ntext
> as
> BEGIN
> BEGIN TRAN
> SET DATEFORMAT dmy
> declare @.phone_id int
> declare @.lastphonenum nvarchar(20)
> DECLARE @.hdoc int
> EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XMLData
> --CALL PERIOD
> declare @.callperiod datetime
> declare @.callperiod_id int
> select @.callperiod = callperiod from OPENXML (@.hdoc, '/bill', 2) with
> (callperiod nvarchar(20))
> IF ( @.@.ROWCOUNT > 0)
> BEGIN
> EXEC sp_addcallperiod @.callperiod, @.callperiod_id OUTPUT
> END
> ELSE BEGIN
> EXEC sp_xml_removedocument @.hdoc
> RAISERROR ('' ' '? callperiod', 16, 1)
> ROLLBACK TRAN
> return -1
> END
> -- ' '? ? summary
> declare @.phonenum varchar(20)
> declare @.phonesummary money
> declare @.summary_id int
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone', 2)
> with (phonenumber nvarchar(20) './phonenumber',
> phonesummary money './summary'
> )
> OPEN phonecursor
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.phonesummary
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --'? '? ''
> exec sp_addphone @.phonenum, @.phone_id OUTPUT
> --'? summary
> exec sp_addphonesummary @.phonesummary, @.phone_id, @.callperiod_id,
> @.summary_id OUTPUT
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.phonesummary
> END
>
> CLOSE phonecursor
> DEALLOCATE phonecursor
> --operaggr
> declare @.oaname nvarchar(128)
> declare @.oasum money
> declare @.aggr_id int
> select @.lastphonenum = ''
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/operaggrs/operaggr', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> oaname nvarchar(128) 'oaname',
> oasum money 'oasum'
> )
> OPEN phonecursor
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.oaname, @.oasum
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --'' ID ' ''
> IF @.lastphonenum <> @.phonenum
> BEGIN
> exec sp_addphone @.phonenum, @.phone_id OUTPUT
> select @.lastphonenum = @.phonenum
> END
> --'? ''
> exec sp_addphoneoperationaggr @.oasum, @.phone_id, @.callperiod_id,
> @.oaname, @.aggr_id OUTPUT
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.oaname, @.oasum
> END
> CLOSE phonecursor
> DEALLOCATE phonecursor
>
> --callaggr
>
> declare @.catarget nvarchar(256)
> declare @.caquant nvarchar(20)
> declare @.casum money
> declare @.caggr_id int
> select @.lastphonenum = ''
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> catarget nvarchar(256) 'catarget',
> caquant nvarchar(256) 'caquant',
> casum money 'casum'
> )
> OPEN phonecursor
>
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.catarget, @.caquant, @.casum
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --'' ID ' ''
> IF @.lastphonenum <> @.phonenum
> BEGIN
> exec sp_addphone @.phonenum, @.phone_id OUTPUT
> select @.lastphonenum = @.phonenum
> END
> --'? ''
> exec sp_addphonecallaggr @.catarget, @.caquant, @.casum, @.phone_id,
> @.callperiod_id, @.caggr_id OUTPUT
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.catarget, @.caquant, @.casum
> END
>
>
> CLOSE phonecursor
> DEALLOCATE phonecursor
>
> --'
> --THIS SECTION FAILS!!!!
> declare @.i int
> select @.i = 0
> declare @.call_id int
> declare @.price money
> declare @.pszone nvarchar(128)
> declare @.vtkzone nvarchar(128)
> declare @.ctchar char(1)
> declare @.ctype nvarchar(20)
> declare @.amount nvarchar(20)
> declare @.direct int
> declare @.ctime nvarchar(30)
> declare @.dt datetime
> select @.lastphonenum = ''
>
>
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/calls/call', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> price money 'price',
> pszone nvarchar(128) 'pszone',
> vtkzone nvarchar(128) 'vtkzone',
> ctchar char(1) 'ctchar',
> ctype nvarchar(20) 'ctype',
> amount nvarchar(20) 'amount',
> direct int 'direct',
> ctime nvarchar(30) 'ctime'
> )
> OPEN phonecursor
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.price, @.pszone, @.vtkzone,
> @.ctchar, @.ctype, @.amount, @.direct,
> @.ctime
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> select @.dt = cast(@.ctime as datetime)
> --'' ID ' ''
> IF @.lastphonenum <> @.phonenum
> BEGIN
> exec sp_addphone @.phonenum, @.phone_id OUTPUT
> select @.lastphonenum = @.phonenum
> END
>
> exec sp_addcall @.dt, @.amount, @.price, @.pszone, @.vtkzone, @.ctype,
> @.ctchar,
> @.phone_id, @.direct, @.callperiod_id, @.call_id OUTPUT
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.price, @.pszone, @.vtkzone,
> @.ctchar, @.ctype, @.amount, @.direct,
> @.ctime
> end;
>
> CLOSE phonecursor
> DEALLOCATE phonecursor
> EXEC sp_xml_removedocument @.hdoc
> COMMIT TRAN
> return 0
> END
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> --
> Structure of XML file is like
> <?xml version="1.0"?>
> <bill>
> <callperiod>30.11.2004</callperiod>
> <phones>
> <phone>
> <phonenumber>0923061267</phonenumber>
> <summary>241.9676</summary>
> <operaggrs>
> <operaggr>
> <oaname>WAP (')</oaname>
> <oasum>1.8311</oasum>
> </operaggr>
> <operaggr>
> <oaname>''? '?/ '' ''?
> '? ' '</oaname>
> <oasum>5.5117</oasum>
> </operaggr>
> </operaggrs>
> <callaggrs>
> <callaggr>
> <catarget>SMS '?: 12 '?</catarget>
> <caquant>1 '</caquant>
> <casum>0.75</casum>
> </callaggr>
> <callaggr>
> <catarget>SMS ''</catarget>
> <caquant>4 '</caquant>
> <casum>0</casum>
> </callaggr>
> </callaggrs>
> <calls>
> <call>
> <price>0</price>
> <pszone></pszone>
> <vtkzone></vtkzone>
> <ctchar></ctchar>
> <ctype>'?.</ctype>
> <amount>0: 01</amount>
> <direct>0</direct>
> <ctime>12.11.2004 15:15:00</ctime>
> </call>
> <call>
> <price>0</price>
> <pszone></pszone>
> <vtkzone></vtkzone>
> <ctchar></ctchar>
> <ctype>'?.</ctype>
> <amount>0: 01</amount>
> <direct>1</direct>
> <ctime>12.11.2004 15:15:00</ctime>
> </call>
> </calls>
> </phone>
> <phone>
> ........
> </phone>
> ...........
> </phones>
> </bill>
> --
> there are about 120 <phone> nodes and about 50000 total <call> nodes in it|||-- You can use @.temp/#temp tables and xml "metaproperties"
-- (see SQL Help files) to avoid cursors as follows:
-- Note that this is completely untested code.
-- I have implemented the concepts successfully, and
-- the code parser doesn't complain, but I didn't
-- put forth the effort to build some test data
-- or tables.
CREATE PROCEDURE parseXML (@.XMLData ntext)
AS
BEGIN
BEGIN TRAN
SET DATEFORMAT dmy
declare @.callperiod datetime
declare @.callperiod_id int
declare @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XMLData
--CALL PERIOD
SELECT @.callperiod = callperiod from OPENXML (@.hdoc, '/bill', 2) WITH
(callperiod nvarchar(20))
IF (@.@.ROWCOUNT > 0)
BEGIN
EXEC sp_addcallperiod @.callperiod,
@.callperiod_id OUTPUT
END
ELSE
BEGIN
EXEC sp_xml_removedocument @.hdoc
RAISERROR ('' ' '? callperiod', 16,
1)
ROLLBACK TRAN
return -1
END
-- ' '? ? summary
DECLARE @.t_phonesummary table (
phone_id int
,phonenum nvrchar(20)
,phonesummary money
,phone_node_id int
)
INSERT INTO @.t_phonesummary (
phonenum
,phonesummary
,phone_node_id
)
SELECT
XMLPhone.phonenum
,XMLPhone.phonesummary
,XMLPhone.phone_node_id
FROM OPENXML (@.hdoc, '/bill/phones/phone', 2)
WITH (
phonenumber nvarchar(20) 'phonenumber'
,phonesummary money 'summary'
,phone_node_id int '@.mp:id'
) AS XMLPhone
-- insert phone numbers that don't already exist
-- if you allow duplicates you could employ a cursur for just this
table, updating
-- t_phonesummary.phone_id = SCOPE_IDENTITY() as you go, with the
remainder of the code
-- functioning without cursors.
INSERT INTO PhoneTable (
phonenum
)
SELECT DISTINCT
t_phonesummary.phonenum
FROM @.t_phonesummary t_phonesummary
WHERE NOT EXISTS (SELECT 1 FROM PhoneTable WHERE phonenum =
t_phonesummary.phonenum)
-- fetch the keys for every phone record (new and old)
UPDATE @.t_phonesummary
SET phone_id = PhoneTable.phone_id
FROM @.t_phonesummary t_phonesummary
INNER JOIN PhoneTable
ON PhoneTable.phonenum = t_phonesummary.phonenum
-- insert phone summary records
-- (assumes no conflicting records exist - you could check if you need
to)
INSERT INTO PhoneSummaryTable (
phone_id
,phonesummary
,callperiod_id
)
SELECT
t_phonesummary.phone_id
,t_phonesummary.phonesummary
,@.callperiod_id
FROM @.t_phonesummary t_phonesummary
-- op aggr
DECLARE @.t_operationaggr table (
oaname nvarchar(128)
,oasum money
,phone_node_id int
)
INSERT INTO @.t_operationaggr (
oaname
,oasum
,phone_node_id
)
SELECT
XMLOpAggr.oaname
,XMLOpAggr.oasum
,XMLOpAggr.phone_node_id
FROM OPENXML (@.hdoc, '/bill/phones/phone/operaggrs/operaggr', 2)
WITH (
oaname nvarchar(128) 'oaname'
,oasum money 'oasum'
,phone_node_id int '../../@.mp:id'
) AS XMLOpAggr
INSERT INTO OperationAggrTable (
phone_id
,oaname
,oasum
,callperiod_id
)
SELECT
t_phonesummary.phone_id
,t_operationaggr.oaname
,t_operationaggr.oasum
,@.callperiod_id
FROM @.t_operationaggr t_operationaggr
INNER JOIN @.t_phonesummary t_phonesummary
ON t_phonesummary.phone_node_id =
t_operationaggr.phone_node_id
--callaggr
DECLARE @.t_callaggr table (
catarget nvarchar(256)
,caquant nvarchar(256)
,casum money
,phone_node_id int
)
INSERT INTO @.t_callaggr (
catarget
,caquant
,casum
,phone_node_id
)
SELECT
XMLCallAggr.catarget
,XMLCallAggr.caquant
,XMLCallAggr.casum
,XMLCallAggr.phone_node_id
FROM OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
WITH (
catarget nvarchar(256) 'catarget'
,caquant nvarchar(256) 'caquant'
,casum money 'casum'
,phone_node_id int '../../@.mp:id'
) AS XMLCallAggr
INSERT INTO CallAggrTable (
phone_id
,catarget
,casum
,callperiod_id
)
SELECT
t_phonesummary.phone_id
,t_callaggr.catarget
,t_callaggr.casum
,@.callperiod_id
FROM @.t_callaggr t_callaggr
INNER JOIN @.t_phonesummary t_phonesummary
ON t_phonesummary.phone_node_id = t_callaggr.phone_node_id
--call
-- Due to the size of this recordset, it may be better to use a regular
temp table (#t_call)
-- if you choose to do so, be sure to declare/create the temp table at
the beginning of the proc to
-- minimize recompiles.
DECLARE @.t_call table (
phone_node_id int
,price money
,pszone nvarchar(128)
,vtkzone nvarchar(128)
,ctchar char(1)
,ctype nvarchar(20)
,amount nvarchar(20)
,direct int
,dt datetime
)
INSERT INTO @.t_call (
phone_node_id
,price
,pszone
,vtkzone
,ctchar
,ctype
,amount
,direct
,dt
)
SELECT
XMLCall.phone_node_id
,XMLCall.price
,XMLCall.pszone
,XMLCall.vtkzone
,XMLCall.ctchar
,XMLCall.ctype
,XMLCall.amount
,XMLCall.direct
,cast(XMLCall.ctime as datetime)
FROM OPENXML (@.hdoc, '/bill/phones/phone/calls/call', 2)
WITH (
phone_node_id int '../../@.mp:id'
,price money 'price'
,pszone nvarchar(128) 'pszone'
,vtkzone nvarchar(128) 'vtkzone'
,ctchar char(1) 'ctchar'
,ctype nvarchar(20) 'ctype'
,amount nvarchar(20) 'amount'
,direct int 'direct'
,ctime nvarchar(30) 'ctime'
) AS XMLCall
INSERT INTO CallTable (
phone_id
,price
,pszone
,vtkzone
,ctchar
,ctype
,amount
,direct
,dt
,callperiod_id
)
SELECT
t_phonesummary.phone_id
,t_call.price
,t_call.pszone
,t_call.vtkzone
,t_call.ctchar
,t_call.ctype
,t_call.amount
,t_call.direct
,t_call.dt
,@.callperiod_id
FROM @.t_call t_call
INNER JOIN @.t_phonesummary t_phonesummary
ON t_phonesummary.phone_node_id = t_call.phone_node_id
EXEC sp_xml_removedocument @.hdoc
COMMIT TRAN
return 0
END
GO
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!