openxml insert - SQL Server Forums

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 insert

Author

Topic

ftsoft
Starting Member

22 Posts

Posted - 2009-10-29 : 14:22:46

In the code below, I get 2 rows generated, but the second row has the same value as the first (105 instead of 115). Can anyone help with this.

xml as follows

<E02>
...
<E02_06>105</E02_06>
<E02_06>115</E02_06>
</E02>

Stored Proc is as follows

INSERT INTO E02_06(fk_E01, E02_06)
SELECT @ParentID, E02_06
FROM OPENXML(@index, 'E02',2)
WITH (fk_E01 INT, E02_06 INT)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-29 : 15:21:03

can you please show us the full xml? how are you getting @parentid

DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(max) -- or xml type
declare @ParentID int
declare @index int

SET @xmlDocument = N'<E02>
<E02_06>105</E02_06>
<E02_06>115</E02_06>
</E02>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

SELECT @ParentID, E02_06
FROM OPENXML(@docHandle, 'E02',2)
WITH (fk_E01 INT, E02_06 INT)

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

ftsoft
Starting Member

22 Posts

Posted - 2009-10-29 : 21:21:16

Posting further documentation. We were stuck with the rather lame xml hierarchy.

the relevant xml

- <DATA_RECORD>
- <E01>
<E01_01>2009128</E01_01>
<E01_02>FTSoft</E01_02>
<E01_03>Tipp City IRS</E01_03>
<E01_04>V01.1</E01_04>
</E01>
- <E02>
<E02_01>55-027</E02_01>
<E02_04>30</E02_04>
<E02_05>75</E02_05>
<E02_06>105</E02_06>
<E02_06>115</E02_06>
<E02_07>150</E02_07>
<E02_08>225</E02_08>
<E02_09>290</E02_09>
<E02_10>360</E02_10>
<E02_12>303</E02_12>
<E02_20>390</E02_20>
</E02>
snip

the SP

ALTER PROCEDURE [dbo].[OpenXMLTest]
(
@newRun Varchar(8000),
@id VARCHAR(10)
)
AS
BEGIN
DECLARE @index int

DECLARE @ParentID int
SELECT @ParentID=pk_E01 FROM E01 Where E01_01=@id

EXEC sp_xml_preparedocument @index OUTPUT, @newRun

UPDATE E02
SET E02_01=xmlTable.E02_01, E02_04=xmlTable.E02_04, E02_05=xmlTable.E02_05,
E02_12=xmlTable.E02_12, E02_20=xmlTable.E02_20
FROM OPENXML(@index, 'DATA_RECORD/E02',2)
WITH (E02_01 VARCHAR(7), E02_04 INT, E02_05 INT, E02_12 VARCHAR(15), E02_20 INT)xmlTable
WHERE E02.pk_E01 = @ParentID

INSERT INTO E02_06(fk_E01,E02_06)
SELECT @ParentID,E02_06
FROM OPENXML(@index, 'DATA_RECORD/E02',2)
WITH (E02_06 INT)
END

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources