CODEWITHSUNDEEP
stored-proceduressql-server-2008-r2sql-server-openxml
Muhammad Nazri
Muhammad Nazri

Reputation: 25

SELECT * FROM OPENXML IN STORED PROCEDURE returns empty row

I have a problem using openxml function in my stored procedure, as you can see, the select statement returns empty row. I suspect the sql couldn't read the xml directories.this is the first time i'm using this function, so i am not sure is this the right way to use it.btw i am using sql server 2008 r2. here's the code to reproduce the problem.

Declare @hddoc int
Declare @Doc xml 

SET @Doc = ' <DataSet>
     <tblEmp>
      <name>Nazri</name> 
      <designation>Developer</designation>  
    </tblEmp>
    <tblEmp>
      <name>Jibin</name> 
 <designation>System Analyst</designation> 
   </tblEmp>
          </DataSet>'

EXEC SP_XML_PREPAREDOCUMENT @hddoc OUTPUT, @Doc

SELECT @hddoc

DECLARE @iDoc INT
SET @iDoc = 1

SELECT *
FROM OPENXML(@iDoc,'/Dataset/tblEmp')
   WITH ( name varchar(50) '@name',
   designation varchar(50) '@designation'
    )

Upvotes: 1

Views: 3126

Answers (2)

Rafał Wojtaszek
Rafał Wojtaszek

Reputation: 668

Change your query to:

CREATE PROCEDURE AddData
( @data XML)
AS
BEGIN
DECLARE
    @handle INT,
    @PrepareXmlStatus INT

EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @Data

INSERT INTO tblEmp (name,designation)
SELECT *
FROM OPENXML(@handle,'/DataSet/tblEmp',2)
   WITH ( name varchar(50) 'name',
          designation varchar(50) 'designation')

EXEC sp_xml_removedocument @handle

END

Upvotes: 0

Tedford
Tedford

Reputation: 2932

The issue is that the supplied table mapping is indicating the the values should be attributes; however, in the XML they are elements. Change your OPENXML statement to be like the following and it should return the expected results.

SELECT * FROM OPENXML(@iDoc,'/DataSet/tblEmp',2)
   WITH ( name varchar(50) 'name',
   designation varchar(50) 'designation'
    )

basically the two changes are to specify element centric mapping and the update the relative xpath to looking for elements instead of attributes.

Upvotes: 1

Related Questions