OpenXML Problem

Question

Post reply

OpenXML Problem

timfoster

Mr or Mrs. 500

Points: 536
More actions
May 18, 2005 at 4:57 am

#67340

I am trying to use OpenXML in my db. I read the article in the SQLCentral daily email and tested it using the following code:
declare @xmlstr varchar(4000)
select @xmlstr = '<RECORD>
                            <EMPLOYEE>
                                <NAME>Test 1</NAME>
                                <SALARY>1000</SALARY>
                                <DEPTID>10</DEPTID>
                            </EMPLOYEE>
                            <EMPLOYEE>
                                <NAME>Test 2</NAME>
                                <SALARY>2000</SALARY>
                                <DEPTID>10</DEPTID>
                            </EMPLOYEE>
                            <EMPLOYEE>
                                <NAME>Test 3</NAME>
                                <SALARY>3000</SALARY>
                                <DEPTID>10</DEPTID>
                            </EMPLOYEE>
                            <EMPLOYEE>
                                <NAME>Test 4</NAME>
                                <SALARY>4000</SALARY>
                                <DEPTID>10</DEPTID>
                            </EMPLOYEE>
                            <EMPLOYEE>
                                <NAME>Test 5</NAME>
                                <SALARY>5000</SALARY>
                                <DEPTID>10</DEPTID>
                            </EMPLOYEE>
                        </RECORD>'
EXEC TestOpenXML @xmlstr
select * from test
This worked fine and inserted the five records into Test. When I cam to use it in real enviroment, it failed. I went back to the example and ran it again, but this also now fails. The call to the proc TestOpenXML works fine but it just inserts one row of Nulls. When I try it on my 'real' table I get a can't insert Nulls into column x error (which is correct, I can't). When I remove the Not Null parameter I get the same error as in the test.
The procedure is below fyi. Can anyone see what is wrong? I really want to use this in a routine that would normally have to make either numerous db calls or run some dynamic sql to insert rows, neither of which are as good as using OpenXML.
CREATE PROCEDURE dbo.TestOpenXML
@strXML VARCHAR(2000)
AS
DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML
BEGIN TRANSACTION
INSERT INTO Test
( Name,
  Salary,
  DeptID)
SELECT
  NAME,
  SALARY,
  DEPTID
FROM OPENXML(@XMLDocPointer,'/RECORD/EMPLOYEE/',2)
WITH
( NAME VARCHAR(50),
  SALARY INT,
  DEPTID INT)
COMMIT
EXEC sp_xml_removedocument @XMLDocPointer
RETURN

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 1

I think you slipped in an extra slash at the end of the xpath

i.e. '/RECORD/EMPLOYEE/' should be '/RECORD/EMPLOYEE'

timfoster Mr or Mrs. 500 Points: 536 More actions · Answer 2

jt-75, you are my hero!!!!! I love you!!!!! If I was a girl (and you were, or are, a boy) I'd want to have your babies!!!!!

My life is complete again. My code runs like the express train it should have been and not the overnighter (or direct express as Silverlink like to call it!!) from London to Watford that my errant forward slash caused it to become.

Thanks!