What is wrong with this query?

Question

Post reply

What is wrong with this query?

kuppurajm

SSCommitted

Points: 1593
More actions
November 24, 2010 at 8:49 pm

#229058

hi,

I have write the query like

-----------------------------------------

insert into tbl_xml(EmpId,EmpName)

SELECT

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

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

FROM @Data.nodes('dataSet/Items') as tabx(colx)

---------------------------------------------------------------------

It works fine.

But I am trying to write this same query like it gives an error

query is

------------------------------------------------------

declare @Cmd varchar(max);

set @Cmd='insert into tbl_xml(EmpId,EmpName)

SELECT

colx.query(''data(id[1]'',''int'') as EmpId,

colx.query(''data(name[1])'',''varchar'') as EmpName

FROM @Data.nodes(''dataSet/Items'') as tabx(colx)'

exec(@Cmd);

------------------------------------------------------------

Viewing 12 posts - 1 through 11 (of 11 total)

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

WayneS SSC Guru Points: 95405 More actions · Answer 1

kuppurajm (11/24/2010)

hi,

I have write the query like

-----------------------------------------

insert into tbl_xml(EmpId,EmpName)

SELECT

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

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

--cast(colx.query('data(id) ') as varchar) as EmpId,

--cast(colx.query('data(name) ') as varchar) as EmpName

FROM @Data.nodes('dataSet/Items') as tabx(colx)

---------------------------------------------------------------------

It works fine.

But I am trying to write this same query like it gives an error

query is

------------------------------------------------------

declare @Cmd varchar(max);

set @Cmd='insert into tbl_xml(EmpId,EmpName)

SELECT

colx.query(''data(id[1]'',''int'') as EmpId,

colx.query(''data(name[1])'',''varchar'') as EmpName

FROM @Data.nodes(''dataSet/Items'') as tabx(colx)'

exec(@Cmd);

------------------------------------------------------------

1. What error is it giving you?

2. The dynamic sql is not the same as the query that works. Specifically, in the dynamic you are using colx.query; in the regular you are using colx.value. (The regular query has the .query stuff remarked out.)

3. You don't need dynamic sql for the query shown - why are you bothering with it?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

kuppurajm SSCommitted Points: 1593 More actions · Answer 2

kuppurajm

SSCommitted

Points: 1593

November 24, 2010 at 8:56 pm

#1255430

hi,

It gives error like

(Must declare the scalar variable "@Data")

but @Data is xml type parameter of sp.

kuppurajm SSCommitted Points: 1593 More actions · Answer 3

because I need to specify columns name as variable dynamically

so that I am expecting this to run with exec

now I am using value fun in both of the cases but it give same error

------------------------------

SELECT

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

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

FROM @Data.nodes('dataSet/Items') as tabx(colx)

declare @Cmd varchar(max);

set @Cmd='SELECT

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

colx.value(''name[1])'',''varchar'') as EmpName

FROM @Data.nodes(''dataSet/Items'') as tabx(colx)'

exec(@Cmd);

Anam Verma SSCertifiable Points: 5365 More actions · Answer 4

the problem here looks like that @Data variable is not in the scope when you execute @Cmd. you need to declare @Data like



declare @Cmd varchar(max);

set @cmd = 'declare @data <.......compelte declaration and set the variable with expected values.....> '

set @Cmd=@Cmd + 'SELECT

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

			colx.value(''name[1])'',''varchar'') as EmpName

			FROM @Data.nodes(''dataSet/Items'') as tabx(colx)'

exec(@Cmd);

kuppurajm SSCommitted Points: 1593 More actions · Answer 5

Thank you very much

now it works fine.

but my requirement is I want to specify column name and data type dynamicaly

how to specify dynamically with this query

------------------------------------------

declare @sql varchar(max)

set @sql='declare @Data xml'

set @sql=@sql + ' set @Data=''<dataSet>

<Items>

</Items>

<Items>

</Items>

<Items>

</Items>

</dataSet>'''

set @sql=@sql + 'SELECT

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

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

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

print(@sql)

exec(@sql)

----------------------------------------------------

This query works fine

but instead of ''id[1]'',''int'' I need to use variable

nageshp Right there with Babe Points: 719 More actions · Answer 6

nageshp

Right there with Babe

Points: 719

November 24, 2010 at 10:38 pm

#1255444

declare @Cmd varchar(max);

set @Cmd='DECLARE @DATA XML

SET @DATA=''<dataSet> <Items><ID>5 </ID> </Items><name>santhose</name></dataSet> '' SELECT

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

colx.value(''name[1])'',''varchar'') as EmpName

FROM @Data.nodes(''dataSet/Items'') as tabx(colx)'

exec(@Cmd)

nageshp Right there with Babe Points: 719 More actions · Answer 7

nageshp

Right there with Babe

Points: 719

November 24, 2010 at 11:05 pm

#1255446

Hi,

You need to declare two input parameters for each column

1) columnname

2) datatype.

so that u can dynamically declare them.

adrian.facio SSCrazy Points: 2406 More actions · Answer 8

You can try something like this for concat FieldName and/or types to a query.

DECLARE @Field2 AS VARCHAR(50)

SET @Field2='FieldTwo'

DECLARE @Field3 AS VARCHAR(50)

SET @Field3='''ID[0]'''

DECLARE @Type3 AS VARCHAR(50)

SET @Type3='''Int'''

DECLARE @SQL AS VARCHAR(100)

SET @SQL='Select '+@Field1+','+@Field2+', cox.value('+@Field3+','+@Type3+') from ATable'

print(@SQL)

The output os this query was:

Select 'FieldOne',FieldTwo, cox.value('ID[0]','Int') from ATable

I'm pretty sure you already know this, but if you have the field names and types as parameters there is no need to declare the vars.

Hope it helps.

adrian.facio SSCrazy Points: 2406 More actions · Answer 9

sorry i did not copy the first two statemetens. Here we go again.

DECLARE @Field1 AS VARCHAR(50)

SET @Field1='''FieldOne'''

DECLARE @Field2 AS VARCHAR(50)

SET @Field2='FieldTwo'

DECLARE @Field3 AS VARCHAR(50)

SET @Field3='''ID[0]'''

DECLARE @Type3 AS VARCHAR(50)

SET @Type3='''Int'''

DECLARE @SQL AS VARCHAR(100)

SET @SQL='Select '+@Field1+','+@Field2+', cox.value('+@Field3+','+@Type3+') from ATable'

print(@SQL)

output:

Select 'FieldOne',FieldTwo, cox.value('ID[0]','Int') from ATable

adrian.facio SSCrazy Points: 2406 More actions · Answer 10

Im miss the first part sorry.

DECLARE @Field1 AS VARCHAR(50)

SET @Field1='''FieldOne'''

DECLARE @Field2 AS VARCHAR(50)

SET @Field2='FieldTwo'

DECLARE @Field3 AS VARCHAR(50)

SET @Field3='''ID[0]'''

DECLARE @Type3 AS VARCHAR(50)

SET @Type3='''Int'''

DECLARE @SQL AS VARCHAR(100)

SET @SQL='Select '+@Field1+','+@Field2+', cox.value('+@Field3+','+@Type3+') from ATable'

print(@SQL)

output:

Select 'FieldOne',FieldTwo, cox.value('ID[0]','Int') from ATable

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

jonetiger (11/24/2010)

I can not find it, because I am new about it. I give you ans come back later.

cremated remains jewelry[/url]

Freakin' low life spammer. Spam reported.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions