June 6, 2008 at 5:23 am
HI,
I am trying to use a variable in Value metohd of XML datatype.
The syntax for Value method is
value (XQuery, SQLType)
if i use varchar as SQLTYPE it works fine , but if i use Int as the SQType it gives an error.
Here is my query
Declare @varPath varchar(100)
set @varPath='''(/PurchaseOrder/MemberNo)[1]'''
select TransactionData.value('sql:variable("@varPath")','int')
from dbo.OrderMgmtUnTyped_ApplicationData
ErrorMsg
Conversion failed when converting the nvarchar value ''(/PurchaseOrder/MemberNo)[1]'' to data type int.
From the error it seems that instead of typecasting the value of PurchaseOrder/MemberNo)[1] it is converting the string .
Can anyone help me please.
June 10, 2008 at 11:20 am
If you change the SQLTYPE to varchar the query will run but it will not give you the desired results.
It will return
(/PurchaseOrder/MemberNo)[1]
Therefore when you change the datatype to int it gives you a casting error because it cannot convert the above mentioned string to int.
Just change
select TransactionData.value('sql:variable("@varPath")','int')
from dbo.OrderMgmtUnTyped_ApplicationData
to
select TransactionData.value('(/PurchaseOrder/MemberNo)[1]','int')
from dbo.OrderMgmtUnTyped_ApplicationData
and every thing will work fine.
Hope this helps!
June 10, 2008 at 10:01 pm
Yes that will work , but i want to make is dynamic so that i can alteast pass the node name and get the value
I got the solution to my problem.This may be helpful for others
Declare @varPath varchar(100)
set @varPath='MemberNo'
select TransactionData.value('(/PurchaseOrder/*[local-name()=sql:variable("@varPath")])[1]','int')
from dbo.OrderMgmtUnTyped_ApplicationData
As the root node is always going to be the same i have hardcoded that but the MemberNo node is dynamic. I can pass any other node name and get the resuly dynamically.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply