March 28, 2010 at 6:47 am
Hello i have OPENROWSET query witch retrieves data from a database and put the resultset in a variable
SET @sql = 'SELECT GenDate_V,MIGversion INTO ##t
FROM OPENROWSET(''SQLNCLI'', ''Server=127.0.0.1;Trusted_Connection=yes;'',
''SELECT MIGversion,GenDate_V
FROM Model.dbo.message
WHERE MsgId LIKE '''''+ @MsgId + ''''''') AS message;';
then i use For xml path and returns the data from the database using a temprary global table like this
EXEC (@sql)
SET @mVersion =
(SELECT TOP 1 MIGversion
FROM ##t
FOR XML PATH);
where @mVersion is the returned xml dokument, now i want to use xquery to obtain data from the resultset using xquery and this is the returned xml dokument
from xml path
<row>
<MIGversion>v1.0 2005-07-08</MIGversion>
</row>
No i wants to get the value of MIGversiond and add to another xml dokument, i have a existing xquery witch is modifying that new xml doc like this
and i have tried getting the value of @mVersion and add to a varchar varable, @lim, but it only return 1 instead of the value of MIGversion
Can someone please help me me obtaining the correct value from MIGversion???
DECLARE @lim nvarchar(100)
SET @lim = convert(nvarchar(100),@mVersion.value('/MIGversion[1]', 'nvarchar(50)'))
PRINT @lim
SELECT @mVersion
SET @apprec.modify('
insert <MIGversion> {sql:variable("@lim")} </MIGversion>into (/AppRec)[1]
')
Many thanks, i andvance for your advise
Best Regards jfallsen
March 28, 2010 at 8:25 am
A very recent article on this site covered this topic in depth:
March 28, 2010 at 12:16 pm
Ok thanks i made it
jfallsen
March 28, 2010 at 8:28 pm
Good. I would encourage you to read Jacob's other articles on XML too, at some stage. They are very good, and quite comprehensive.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply