Riz Riz - 3 months ago 9
SQL Question

Error "must declare scalar variable" when using a in memory table in T-SQL

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 an error


Must declare the scalar variable


This 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)