In SQL Server 2008 (yes, I know) how do you write an XPath to find nodes with different names?

For example, if I had either of the following XML...

DECLARE @XML XML = '<data><id>1</id><id>2</id></data>'
or...
DECLARE @XML XML = '<d><i>1</i><i>2</i></d>'

I can write the following which will work for the first XML, but wouldn't work for the second XML.

SELECT X.value('.','int') FROM @XML.nodes('/data/id') AS X(X)

I was going to use a union (/data/id | /d/i) but SQL Server apparently doesn't support unions.

The simple solution is to do the following...

SELECT X.value('.','int') FROM @XML.nodes('/data/id') AS X(X)
UNION
SELECT X.value('.','int') FROM @XML.nodes('/d/i') AS X(X)

... but I would prefer it if there was a single XPath solution instead

CodePudding user response：

I've just found this answer (which is for general XPath, not SQL based) and it has given me the solution.

Use the , character and wrap in brackets...

SELECT X.value('text()[1]','int') FROM @XML.nodes('(/data/id,/d/i)') AS X(X)

(Note, I've also updated the '.' to be 'text()[1]' as per the suggestion by @YitzhakKhabinsky)