CODEWITHSUNDEEP
xmlt-sqlxquery
koumides
koumides

Reputation: 2504

Using variable to TSQL + XQuery

All,

I have the following code:

--xml is already declared and populated with an xml document

SELECT
ent.query('TradeId').value('.','VARCHAR(10)') TradeId
FROM                 
@xml.nodes('//table/trade[1]') Col(ent)

How can I have the expression //table/trade[1] as a variable so I can increase the [1] in a loop ? For example I would like something like the below which is not working:

DECLARE @KLM varchar(100)
SET @KLM= '//table/trade[1]'
SELECT
ent.query('TradeId').value('.','VARCHAR(10)') TradeId
FROM                 
@xml.nodes(@KLM) Col(ent)

Any ideas?

Thanks,

M

Upvotes: 1

Views: 627

Answers (1)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

If you remove the positional reference from your .nodes() method, you should get a resultset containing all of the TradeId values.

SELECT 
ent.query('TradeID').value('.','VARCHAR(10)') TradeId 
FROM                  
@xml.nodes('//table/trade') Col(ent)

You should then be able to iterate through that resultset, although I would like to add the standard caveat about "iteration in a relational model is generally a bad idea".

Does that help? If not, is there more to the story?

Upvotes: 1

Related Questions