Avatar of Susan Stevenson
Susan StevensonFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

Dynamic SQL statements to parse XML

I am trying to insert the below dynamic sql but I am getting the following errors:

select  
XmlData.value('((/quote/)[sql:variable("@EquipName")])[1]' , 'nvarchar(100)')
FROM dbo.QuoteXML

Msg 9341, Level 16, State 1, Line 2
XQuery [dbo.QuoteXML.XMLData.value()]: Syntax error near ')', expected a step expression.


I have also tried

select  
XMLData.value('(/quote/'+ @EquipName+ ')[1]','varchar(100)')
FROM dbo.QuoteXML

Msg 8172, Level 16, State 1, Line 57
The argument 1 of the XML data type method "value" must be a string literal.

Does anyone know how I should construct this line of code? @EquipName = 1

Many Thanks,
Susan
Microsoft SQL ServerXML

Avatar of undefined
Last Comment
Susan Stevenson
ASKER CERTIFIED SOLUTION
Avatar of Jason clark
Jason clark
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Susan Stevenson

ASKER

Hi Jason,

When I use :

1 -
select
XMLData.value('(/quote/quote_id)[1]','varchar(100)')
,XMLData.value('(/quote/option_equipment_code_1_rate_fixed)[1]','varchar(100)')
FROM dbo.QuoteXML

it returns what I need.

However, when I try and convert this to a string (in preparation of substituting the node name for a variable) it errors.  

2-
DECLARE @sql nvarchar(4000)
SET @sql ='select XMLData.value('(/quote/quote_id)[1]',''varchar(100)')
,XMLData.value('(/quote/option_equipment_code_1_rate_fixed)[1]','varchar(100)')
'FROM dbo.QuoteXML'

Then what I want to do is the following:
3 -
DECLARE @sql nvarchar(4000)
SET @sql ='select XMLData.value('(/quote/quote_id)[1]',''varchar(100)')
,XMLData.value('(/quote/'+option_equipment_code_1_rate_fixed+')[1]','varchar(100)')
'FROM dbo.QuoteXML'

I believe that I need to resolve point 2 before I can resolve point 3

Any further help would be appreciated.  I have read the information your provided but I am still stumped

Many Thanks,
Susan
Avatar of Susan Stevenson

ASKER

Got there in the end. it was the conversion to string causing the issue. thanks
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo