openxml multiple multiple values in table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

openxml multiple multiple values in table

Author

Topic

ftsoft
Starting Member

22 Posts

Posted - 2010-01-18 : 11:05:44

I have the following xml

- <E09>
<E09_01>-25</E09_01>
<E09_01>-35</E09_01>
<E09_02>-25</E09_02>
<E09_02>-455</E09_02>
<E09_03 />
<E09_04>-25</E09_04>
<E09_11>-25</E09_11>
<E09_12>-25</E09_12>
<E09_13>-25</E09_13>
<E09_14>-25</E09_14>
<E09_15>-25</E09_15>
<E09_16>-25</E09_16>
</E09>

I know how to deal with, for instance,

<E09_01>-25</E09_01>
<E09_01>-35</E09_01>

with the following openxml sql statement

INSERT INTO E09_A (fk_E01, E09_01)
SELECT @ParentID, E09_01
FROM OPENXML(@index, 'DATA_RECORD/E09/E09_01',1)
WITH (fk_E01 INT, E09_01 VARCHAR(30) 'text()')

but I'm not sure how to deal with both E09_01 and E09_02 being inserted into the same table with the same foreign key. Thanks.

Frank

ftsoft
Starting Member

22 Posts

Posted - 2010-01-18 : 14:24:49

I cobbled together a solution which probably isn't the best, but it works.

DECLARE @E901 Varchar(30)
DECLARE @E902 int

Declare search1 cursor for

SELECT E09_01
FROM OPENXML(@index, 'DATA_RECORD/E09/E09_01',1)
WITH (E09_01 VARCHAR(30) 'text()')

Declare search2 cursor for

SELECT E09_02
FROM OPENXML(@index, 'DATA_RECORD/E09/E09_02',1)
WITH (E09_02 INT 'text()')

Open search1
Open search2
fetch next from search1 into @E901
fetch next from search2 into @E902

while @@fetch_status = 0
Begin

INSERT INTO E09_A (fk_E01, E09_01, E09_02)
VALUES(@ParentID, @E901, @E902)

fetch next from search1 into @E901
fetch next from search2 into @E902

End
Close search1
Close search2

Deallocate search1
Deallocate search2

Frank

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources