February 7, 2009 at 9:05 am
I made a table with an xml datatype column. In this column i put a xml file. There can be more xml files and the have an id. The xmlfile needs to be added or updated in a relational database. Say i have a node called learning_method with the name : English and with a title : my first words. I need to check first if this record exists in de database. When i am querying the xml i only get stuff like . I can't use this to check if the data is allready there.
I need an xquery that gives me back for the name : English and for the title :My first words. When thsi is returned i can query the database to see if they are allready there wich means i have to update otherwise i have to insert.
Is there someone out there who knows how to deal with this???
:hehe:
February 7, 2009 at 11:33 am
Could you please post up a short script creating a temporary table with an XML column and posting a sample of your XML into it? The article below illustrates what I mean.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
If that isn't acceptable , you may find the answer to your question here:
http://qa.sqlservercentral.com/articles/Basics/3117/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 8, 2009 at 6:44 am
I found the answer:
DECLARE @XMLString XML
declare @strName as varchar(50)
declare @tiete as varchar(50)
SET @XMLString = (select xmldata from dbo.Stg_XmlFile where id = 43)
set @strName = (select tab.col.value('name[1]','VARCHAR(20)')AS name from @XMLString.nodes('//learning_method') tab(col))
print @strname
SELECT
tab.col.value('name[1]','VARCHAR(20)')AS name,
tab.col.value('title[1]','VARCHAR(20)')AS title
FROM @XMLString.nodes('//learning_method') tab(col)
this is a satisfactable solution for me. Thanx for the reply.
:hehe:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply