Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem reading XML value from a table in storedproc 1

Status
Not open for further replies.
TeaAddictedGeek

TeaAddictedGeek

Programmer
Apr 23, 1999
271
US
Hi,

I'm trying to read an XML variable returned from a stored proc within another stored proc which is processing it using OpenXML. However, it's not returning any data in the rows.

Here's roughly what the XML looks like that is stored in the table:

<RECORD id="1" city="BOS" date="5/1/2006">
<DATA1 ... ></DATA1>
<DATA2 ... ></DATA2>
</RECORD>

And here's the SQL:

exec getfreezeddata @pDate, @pCity, @pXML output
-- this retrieves the XML from the table by date and city
EXEC sp_xml_preparedocument @idoc OUTPUT, @pXML

SELECT id, date, city FROM
OPENXML(@idoc, '/FREEZE_RECORD', 1)
with (
id int,
date varchar(15),
city varchar(10)
)

What could be going wrong here? I've verified that the XML is indeed well formed by copy/pasting the string into XMLSpy.


Thanks in advance!

"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum
 
Sort by date Sort by votes
I'm not sure what you actually want to see here (because you didn't say), but try this change maybe?

Code: 
SELECT id, date, city FROM 
    OPENXML(@idoc, [b]'/RECORD'[/b], 1) 
with (
    id int,
    date varchar(15),
    city varchar(10)
)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
You are mixing Elements and Attributes in your xml, so the 3 parameter to the OpenXML function should be a 3. Here's some sample code to get you started...

Code: 
[COLOR=blue]Declare[/color] @Xml [COLOR=blue]VarChar[/color](8000)

[COLOR=blue]Set[/color] @Xml = [COLOR=red]'<RECORD id="1" city="BOS" date="5/1/2006">
[/color]  <DATA1>Blue</DATA1>
  <DATA2>Red</DATA2>
</[COLOR=#FF00FF]RECORD[/color]>[COLOR=red]'
[/color]
[COLOR=blue]Declare[/color] @iDoc [COLOR=blue]Int[/color]

[COLOR=blue]exec[/color] sp_xml_preparedocument @idoc [COLOR=blue]OUTPUT[/color], @Xml

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]	OpenXML(@iDoc, [COLOR=red]'RECORD'[/color], 3)
[COLOR=blue]With[/color]	(
		id [COLOR=blue]int[/color],
		city [COLOR=blue]VarChar[/color](20),
		[COLOR=blue]date[/color] [COLOR=#FF00FF]DateTime[/color],
		DATA1 [COLOR=blue]varchar[/color](20),
		DATA2 [COLOR=blue]varchar[/color](20)
		)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Which SQL server version we're talking here? There are new ways to work with XML in SQL Server 2005, I learned about it yesterday from another forum. I post it here (code by Kevin Goff):

Regarding the XML approach for variable lists...there are new XML capabilities in SQL 2005 that eliminate the need for sp_xml_preparedocument and OPENXML. You can basically "shred" the XML using new XQUERY capabilities to create a reusable table-valued User Defined Function. (I covered this in a prior CoDe article):

Code: 
CREATE FUNCTION [dbo].[XML2TableVar] 
(  @XMLString XML) 
RETURNS 
   @tPKList TABLE  ( PKValue int) 

AS 
BEGIN 
            INSERT INTO @tPKList 
              SELECT Tbl.Col.value('.','int') 
                  FROM   @XMLString.nodes('//PKValue') Tbl( Col ) 

   RETURN 
END 

And then use it like this:


DECLARE @XMLString XML 
SET @XMLString ='<Customers> 
                 <Customer> 
                      <PKValue>2</PKValue> 
                      <PKValue>5</PKValue> 
                   </Customer> 
                  </Customers>' 


-- Show the contents 
SELECT  * FROM [dbo].[XML2TableVar] (@XMLString) CustList 

-- Perform a JOIN using the UDF 
SELECT Customers.* FROM Customers 
                        JOIN [dbo].[XML2TableVar] (@XMLString) CustList 
                                    ON Customers.CustomerPK = CustList.PKValue
Obviously, you'd need to modify this to support character columns, but you get the idea...
 
Upvote 0 Downvote
AlexCuse, that was a typo on my part, not reflective of the code. Sorry!

Ilyad: This is SQL Server 2005. Is there any way to do this without declaring a hard-coded XML string? I'm looking to grab the value from the database and it's going to be huge--I don't think varchar(8000) will hold all of the data.

"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum
 
Upvote 0 Downvote
gmmastros, I tried your suggestion and it's still not working. I'm looking to even just try returning the attributes of the main element to test this out and I can't even obtain those.

"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum
 
Upvote 0 Downvote
Double check the attribute names, and remember that XML is case sensitive.

You can copy/paste my example to a query analyzer window and see that it returns data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
The element names are in all caps, and the attribute names are all in lowercase. Everything's consistent in that regard.

Yours worked, so I can only conclude that the problem is with the XML I am getting from the database. It's not giving me any errors so I can't figure out what the problem could be.

"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum
 
Upvote 0 Downvote
Can you post some more sample data?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
I'll try. It's really huge, but I'll give it my best shot by posting a short example of the structure of the data:

<RECORD id="1" city="BOS" date="5/1/2007">
<RECORD_TYPE value="A">
<AREA value="ABC">
<AREA_DATA id="" type=""></AREA_DATA>
<AREA_DATA id="" type=""></AREA_DATA>
<AREA_DATA id="" type=""></AREA_DATA>
</AREA>
<AREA value="DEF">
<AREA_DATA id="" type=""></AREA_DATA>
<AREA_DATA id="" type=""></AREA_DATA>
<AREA_DATA id="" type=""></AREA_DATA>
</AREA>
</RECORD_TYPE>
<RECORD_TYPE value="B">
<AREA_DATA id="" type=""></AREA_DATA>
<AREA_DATA id="" type=""></AREA_DATA>
<AREA_DATA id="" type=""></AREA_DATA>
</RECORD_TYPE>
</RECORD>

Yes, it's done on purpose that RECORD_TYPE of value "B" won't have an AREA underneath it but will have data.

Without giving client specific data away, this is an excellent mimic of the structure and I hope that it helps. Please let me know if you have any other questions.


"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum
 
Upvote 0 Downvote
I don't think you have to declare this string as Varchar 8000. You may try your manipulations with table field directly without using a variable, I think.
 
Upvote 0 Downvote
Yeah, right now I have a stored proc that grabs a column of type xml and returns it as an output parameter. Then I grab that parameter and put it into the procedure like this:

EXEC sp_xml_preparedocument @idoc OUTPUT, @pXML_data

I've tried many ways of grabbing the attributes to get the OPEN_XML to work and figure out how to get the rest in the XML from there, but I can't even obtain the attributes of the main element.

I'm guessing that whatever is being returned from the table may be invalid xml or have invalid characters, but it shouldn't have anything in the whitespace other than spaces and carriage returns--I made sure of that in textpad using view visible space option to checked. Copy/pasting the string in XMLSpy also yielded a well formed XML document.

I'm guessing that maybe the table data should be stored as something else other than XML such as nchar or text and then parsed out, or maybe the database is possessed. :p

"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum
 
Upvote 0 Downvote
I did discover one thing, but it still hasn't helped: SQL Server 2005 only supports UTF encoding of 16, not 8--so I changed that in an update. However, the code still doesn't work, and I still can't even as much as grab the attributes from the main element.

Is there any way to debug the process to see if it's even reading the XML properly? Or at all?

"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum
 
Upvote 0 Downvote
Ok, for fun and giggles I set up a test procedure and added this string to the table:

<RECORD id="1" city="BOS" date="5/1/2006">
<DATA1>Blue</DATA1>
<DATA2>Red</DATA2>
</RECORD>

and then used the procedure which DID work before to get the data:

Declare @iDoc Int

exec getrecorddata @pDate, @pCity, @pXMLdata output
exec sp_xml_preparedocument @idoc OUTPUT, @pXMLdata

Select *
From OpenXML(@iDoc, 'RECORD', 3)
With (
id int,
city VarChar(20),
date DateTime,
DATA1 varchar(20),
DATA2 varchar(20)
)

And it still didn't work.

The only thing I can think of is that there is a disconnect between the output of the first storedproc and what I am putting into sp_xml_preparedocument.

Any ideas? I'm getting closer, I think....

"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum
 
Upvote 0 Downvote
Ok I got it figured out--thanks for everyone's help and patience!!!

"The computer programmer is a creator of universes for which he alone is responsible. Universes of virtually unlimited complexity can be created in the form of computer programs."
-Joseph Weizenbaum
 
Upvote 0 Downvote
So, what did you figure out? Inquiring minds want to know <g>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
582
bharons
bharons
SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
860
SashaBuilder3
SashaBuilder3
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
2K
Chris Miller
Chris Miller
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
713
Chris Miller
Chris Miller
Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
537
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top