write query to select value from xml parameter within exec

Question

write query to select value from xml parameter within exec

kuppurajm

SSCommitted

Points: 1593
More actions
November 24, 2010 at 6:37 am

#388299

hi,
I wrote query to select value from xml parameter with in stored procedure.but i need to write that query within exec
my query is
-----------
Begin
SELECT
colx.value('id[1]','int') AS EmpId,
colx.value('name[1]','VARCHAR(max)') AS EmpName
FROM @data.nodes('dataSet/Items') as x(colx)
End

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'm not clear on what you're asking about.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kuppurajm SSCommitted Points: 1593 More actions · Answer 2

hi,

Just I want to pass datatable as xml parameter to sp

and in sp I need to select values from xml parameter and insert into another table.

if we spcify column directly there is no problem.

I have done this.

but I have pass all the column as parameter by concatenate in .net

so in sql server Instead of this (SELECT

colx.value('id[1]','int') AS EmpId,

colx.value('name[1]','VARCHAR(max)') AS EmpName

FROM @data.nodes('dataSet/Items') as x(colx))

I need to use parameter for spcifing column.

so i want to write this query as dynamic within exec

GSquared SSC Guru Points: 260824 More actions · Answer 3

You'll need to build the query as a string, then execute it.

Something like this:

declare @Cmd varchar(max);

set @Cmd = 'SELECT

colx.value('' + @Col1Parameter + '[1]'',''int'') AS ' + @Col1ParamName + '

FROM @data.nodes(''dataSet/Items'') as x(colx);'

exec(@Cmd);

You can use parameters to indicate the desired column type, of course.

If you have multiple columns, you'll need to pass in multiple values. A table-variable input parameter would allow you to do that, then just build the string from that.

It's going to look complex when you start, but it'll work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon