CODEWITHSUNDEEP
sql-serverxmlt-sql
Iain Ward
Iain Ward

Reputation: 9936

JOIN using a 'LIKE' on an XML Node?

I've got a view that joins tables using a match on an xml node, like this example:

....CROSS APPLY xmlData.Nodes('/element/subElement')....

The problem is we now have elements called 'element2' and 'element3' which don't get picked up. Is there a way to include these, almost using a LIKE on the nodes to pick these up as well?

Something along these lines:

....CROSS APPLY xmlData.Nodes('/element%/subElement')....

or is it a case of adding these extra nodes into the view manually?

Thanks

Upvotes: 1

Views: 260

Answers (1)

cordsen
cordsen

Reputation: 1701

You can use an XQuery expression

CROSS APPLY xmlData.nodes('/*/subElement')

Update

If you need to be more specific you can use

CROSS APPLY xmlData.nodes('/*[substring(local-name(),1,7)="element"]/subElement')

in theroy starts-with would be better than substring but it does not work in SQL-server-2005 and I can't test with SQL-server-2008

Upvotes: 3

Related Questions