Advertisement

04.21.2008 at 02:56PM PDT, ID: 23341253
[x]
Attachment Details

SQL 2000 OpenXML sp_XML_Preparedocument with more than 1 row

Asked by carl- in MS SQL Server, SQL Server 2005

Tags: Microsoft, SQL Server, 2000, sp_XML_PrepareDocument or workaround

Hi
 I have a table with multiple fields, of which 1 field iswith a text field called xmlproperties.  in that field is the following:
keyfield (int), xmlproperties (text)
<Description>some description here</Description>
<Description>another description</Description
...
now this is about useless to me, so I parse it out with the following code.

  The following code works fine to pull out the xml if my select statement only returns 1 row
The problem I have is I want to return approx 500 rows.  When I try and return more than 1 row, I get

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
in short "(select cast(XMLProperties as varchar(1000)) from Device where ID =141)" works
"(select cast(XMLProperties as varchar(1000)) from Device)"  fails

Can someone show me either a way to return more than 1 row, or to create a cursor to parse this to a temp table or any way to pull out the xml from the text field

Thanks

Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

DECLARE @hdoc int
DECLARE @doc varchar(1000)
SET @doc=(select cast(XMLProperties as varchar(1000)) from Device where ID =141)
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
 
select '<ROOT>'+@doc+'</ROOT>' as XMLProperties
SELECT Description
FROM 
    OPENXML(@hdoc,'/ROOT', 2) --Use 2 for elementCentric xml not the default of 1
       WITH (
           Description VARCHAR(100)
            )
-- Remove the internal representation.
exec sp_xml_removedocument @hdoc
[+][-]04.21.2008 at 03:03PM PDT, ID: 21406508

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.21.2008 at 03:11PM PDT, ID: 21406563

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.21.2008 at 03:16PM PDT, ID: 21406608

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: MS SQL Server, SQL Server 2005
Tags: Microsoft, SQL Server, 2000, sp_XML_PrepareDocument or workaround
Sign Up Now!
Solution Provided By: chapmandew
Participating Experts: 1
Solution Grade: A
 
 
[+][-]04.21.2008 at 03:19PM PDT, ID: 21406631

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.21.2008 at 03:20PM PDT, ID: 21406640

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.21.2008 at 03:25PM PDT, ID: 21406663

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.21.2008 at 04:29PM PDT, ID: 21407019

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628