Syntax error using sql:variable in an XML nodes SQL Server query

Question

I've been working on a query to pull XML data from a SQL Server database. I want to extract a specific row from the XML. If I hard code the ID in the .nodes line, it works as intended, but I can't get past a syntax error when replacing the hard coded value with sql:variable and have exhausted my Google-foo

'''SQL
DECLARE @XML XML = '<DynamicModel>
<AvailableElements>
  <Control Id="97a0d1c6-f2b4-4f6f-8d01-f6110f1679af">
  <Property Name="Name" Value="Picklist1" />
  <Property Name="Id" Value="97a0d1c6-f2b4-4f6f-8d01-f6110f1679af" />
  <Property Name="Label" Value="Label value here" />
  <Property Name="SelectedItemId" Value="Value in here" Type="System.Nullable`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsRequired="True" IsReference="True" />
  <Elements />
  <AvailableElements />
</Control>
</AvailableElements>
</DynamicModel>'
DECLARE @ElementID NVARCHAR(100) = '97a0d1c6-f2b4-4f6f-8d01-f6110f1679af'

/* WORKS */
SELECT
codedValue.value('@Name[1]', 'nvarchar(500)') AS ItemName,
codedValue.value('@Value[1]', 'nvarchar(500)') AS ItemValue
FROM @XML.nodes('/DynamicModel/AvailableElements/Control[@Id="97a0d1c6-f2b4-4f6f-8d01-f6110f1679af"]/Property') AS x(codedValue)
WHERE codedValue.value('@Name[1]', 'nvarchar(500)') IN ('Text','Date','SelectedItemId','Label')

/* DOES NOT WORK */
SELECT
codedValue.value('@Name[1]', 'nvarchar(500)') AS ItemName,
codedValue.value('@Value[1]', 'nvarchar(500)') AS ItemValue
FROM    @XML.nodes('/DynamicModel/AvailableElements/Control[@Id="sql:variable("@ElementID")]/Property') AS x(codedValue)
WHERE codedValue.value('@Name[1]', 'nvarchar(500)') IN ('Text','Date','SelectedItemId','Label')
'''

The hard coded version returned two rows which is correct based on the XML. The version using sql:variable errored with the message 'XQuery [nodes()]: Syntax error near '@', expected ']'.'

You've got a stray quote gumming up the works. Use [@Id=sql:variable("@ElementID")]. — Jeroen Mostert
– Jeroen Mostert, Commented May 21, 2019 at 12:41

Dev · Accepted Answer · 2019-05-21 12:43:17Z

1

Your Query should be like this-

SELECT
codedValue.value('@Name[1]', 'nvarchar(500)') AS ItemName,
codedValue.value('@Value[1]', 'nvarchar(500)') AS ItemValue
FROM    @XML.nodes('/DynamicModel/AvailableElements/Control[@Id=sql:variable("@ElementID")]/Property') AS x(codedValue)
WHERE codedValue.value('@Name[1]', 'nvarchar(500)') IN ('Text','Date','SelectedItemId','Label')

answered May 21, 2019 at 12:43

Dev

4204 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax error using sql:variable in an XML nodes SQL Server query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related