scm0sml
asked on
strange results from openxml sql server 2005
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
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
Last Comment
Anthony Perkins
You are going to have to post the Xml document you are using.
ASKER
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>'
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>'
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I think you mean:
FROM OPENXML(@idoc, '/xml/row', 1)
FROM OPENXML(@idoc, '/xml/row', 1)
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.
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.
ASKER
Yes you are right about the /row...
What new methods are they?
What new methods are they?
Read up on "xml Data Type Methods" in SQL Server BOL.
This is what it would look like (correct the obvious typo in xDECLARE)
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)
ASKER
Answered my own question
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
