June 16, 2008 at 12:17 pm
I'm beginning to think I'm cursed. I just can't get either OpenXML() or the value() functions to work. Maybe it's just the way I have my XML document set up, though.
I'm working from a table populated by a trigger. The XML doc looks like this:
(Yikes, code wouldn't insert... See attached)
Basically, AgentID & AgentName are elemental in nature instead of being attributes (all of the values are in the root). And when I try to use different variations on OpenXML() or Value() or Query(), I keep coming up with NULL values or False when I check for Exist(). Is this because these values are Elements instead of Attributes?
I'm new to XML, so maybe I've got my information wrong... Anyway, all the examples I can find on the net seem to have an extra node between the values and the root. I can't find any that reference values directly in the root. Does this mean there isn't anyway of shredding the data I have?
Help appreciated in advance. Thanks,
June 16, 2008 at 12:21 pm
Brandie - you're going to need to run a "find/replace" on the < and > sign's. SSC is "eating" your XML snippet.
Replace them with < and > respectively.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 12:26 pm
Ahh...I think the namespace is messing you up. try this on...
http://www.sqlservercentral.com/Forums/Topic483834-21-1.aspx#bm483865
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 12:41 pm
Hmm. Interesting article. I'm going to have to play with it, though, because it looks like it won't allow me to select the column from the table directly.
Thanks for the info, Matt. I'll let you know if I can make it work.
June 16, 2008 at 1:05 pm
Post your query if you get stuck - you should be able to get at those pretty definitively.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 17, 2008 at 5:36 am
Ahha! I was taking the CAST part of the referenced article too literally. I found a different article on MS's site that references the type of query I'm trying to do with a slightly different namespace config:
http://msdn.microsoft.com/en-us/library/ms189075.aspx
I'm going to see if this works out. I don't see why it shouldn't.
June 17, 2008 at 6:41 am
Darnit. Still coming up with NULL as my result. The XML column has data like below (though there are more elements than I've listed):
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<AgentID>11</AgentID>
<AgentName>Luke Skywalker, Jedi Knight</AgentName>
</row>
The latest code I'm using to try and query is:
Select arecordinfo.value('declare namespace
agents="http://www.w3.org/2001/XMLSchema-instance";
(/agents:root/agents:AgentID)[1]','int') as AgentID from dbo.dba_AgentLog;
arecordinfo is the XML column, dba_AgentLog is my table.
Thoughts?
June 18, 2008 at 7:16 am
Based on the xml you posted, the following code works:
DECLARE @x xml;
set @x=N'
'
Select T.c.value('AgentID[1]','int') as AgentID,
T.c.value('AgentName[1]','nvarchar(100)') as AgentName
from @x.nodes('row') T(c)
June 18, 2008 at 7:18 am
OK. I can't paste the xml here. It's the same as the file you attached.
June 18, 2008 at 7:21 am
Peter,
I'm trying to avoid setting a variable if I can. I want to read directly from the table that I've got set up. I'm going to have a lot of records I'm checking eventually and using the values in the XML datatype to update a data warehouse.
But thanks for the reply anyway. I appreciate it.
June 23, 2008 at 10:11 am
I'm still not getting this to work. Does anyone have any thoughts on how to select from an XML column in a table and shred it to a relational SQL record?
Thanks,
June 23, 2008 at 10:21 am
The syntax peter is getting at also works against table columns. you just have to use CROSS APPLY.
Should be something like this (Fair warning - coding with a substantial head cold here, so YMMV).
Select T.c.value('AgentID[1]','int') as AgentID,
T.c.value('AgentName[1]','nvarchar(100)') as AgentName
from dbo.dba_AgentLog
CROSS APPLY
arecordinfo.nodes('row') T(c)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 23, 2008 at 10:36 am
WHOOHOO!
That's the ticket, Matt! (Head cold or not, you is GENIUS). And I didn't even have to use the namespace declaration to make it work. The syntax you provided worked as is, no modifications!
Veile DANKE!!!
WHOOHOO!
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply