strange results from openxml sql server 2005

scm0sml
scm0sml used Ask the Experts™
on
Hi,

I have the following sp.

Basically I am trying to pass in some xml and will then extract from this into a temp table but at the moment doing a simple select * from is bringing back results I don't understand, this is the first time I have used openxml so would be good if someone could explain and tell me what I am doing wrong.

The results are in an attached spreadsheet.

Thanks in advance. eg.xls
ALTER PROCEDURE dbo.SP_Get_685_Report
(
	@LINEIDXML TEXT
)

AS

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @LINEIDXML

SELECT *
FROM OPENXML(@idoc, '/xml',1) 

EXEC sp_xml_removedocument @idoc

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
You are going to have to post the Xml document you are using.

Author

Commented:
Sorry, am using:
EXEC SP_Get_685_Report '<xml><row Department="70" Category="182" SubCategory="048" LineID="11840" Description="SET - MINNIE SWING TOP AND LEG" /><row Department="70" Category="182" SubCategory="048" LineID="11840" Description="SET - MINNIE SWING TOP AND LEG" /></xml>'
Commented:
I got it working using:
ALTER PROCEDURE dbo.SP_Get_685_Report
(
      @LINEIDXML TEXT
)

AS

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @LINEIDXML

SELECT DISTINCT Department,Category,SubCategory,LineID,Description
FROM OPENXML(@idoc, '/root/row',1)
WITH      (Department  varchar(10),
            Category varchar(10),
            SubCategory varchar(10),
            LineID varchar(20),
            Description varchar(200)
)

EXEC sp_xml_removedocument @idoc
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2012

Commented:
I think you mean:
FROM OPENXML(@idoc, '/xml/row', 1)
Top Expert 2012

Commented:
Also, it is best to explicitly define the nodes, as in:
FROM OPENXML(@idoc, '/xml/row',1)
WITH      (Department  varchar(10) '@Department',
            Category varchar(10) '@Category',
            SubCategory varchar(10) '@SubCategory',
            LineID varchar(20) '@LineID',
            Description varchar(200) '@Description'
)

And you should know that the OPENXML statement has been largely superceded by the new (in SQL Server 2005) Xml Data Type Methods.

Author

Commented:
Yes you are right about the /row...

What new methods are they?
Top Expert 2012

Commented:
Read up on "xml Data Type Methods" in SQL Server BOL.

This is what it would look like (correct the obvious typo in xDECLARE)
xDECLARE	@LINEIDXML Xml

SET @LINEIDXML =  '<xml><row Department="70" Category="182" SubCategory="048" LineID="11840" Description="SET - MINNIE SWING TOP AND LEG" /><row Department="70" Category="182" SubCategory="048" LineID="11840" Description="SET - MINNIE SWING TOP AND LEG" /></xml>' 


SELECT	DISTINCT 
	T.c.value('@Department', 'varchar(10)') Department,
	T.c.value('@Category', 'varchar(10)') Category,
	T.c.value('@SubCategory', 'varchar(10)') SubCategory,
	T.c.value('@LineID', 'varchar(20)') LineID,
	T.c.value('@Description', 'varchar(200)') [Description]
FROM	@LINEIDXML.nodes('/xml/row') T(c)

Open in new window

Author

Commented:
Answered my own question

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial