Riz Riz - 3 months ago 8
SQL Question

error "must declare scalar variable" when using a in memory table in tsql

I am converting a varchar to xml and putting it into a in memory table so that I can query it, but I keep getting "Must declare the scalar variable" error. Below is my code:

Declare @templatexml table
(CurrentTemplateXml XML Not Null)

insert into @templatexml(CurrentTemplateXml)
select convert(xml, convert(nvarchar(max), O.CurrentTemplateXml))
From
BMObject O


select * from (

select T.C.value('@Name', 'nvarchar(max)') as name
from @templatexml.nodes('(ObjectTemplate/Sections/Section)') as T(C)

) as temp

Answer

You declared table variable @templatexml, but you use it like variable of xml type.

It seems that in place of

select T.C.value('@Name', 'nvarchar(max)') as name
from @templatexml.nodes('(ObjectTemplate/Sections/Section)') as T(C)

you want to use

select T.C.value('@Name', 'nvarchar(max)') as name
from @templatexml tx
    cross apply tx.CurrentTemplateXml.nodes('(ObjectTemplate/Sections/Section)') as T(C)
Comments