How to get required XML element from not well formed XML data in SQL server

Question

Hello, In my SQL 2008 database table, I have one column name AUTHOR that contains XML data. The XML is not well formed and has data like below

<Author>
<ID>172-32-1176</ID>
<LastName>White</LastName>
<FirstName>Johnson</FirstName>
<Address>
<Street>10932 Bigge Rd.</Street>
<City>Menlo Park</City>
<State>CA</State>
</Address>
</Author>

Some XML have all of above data and some have just one tag.

<ID>172-32-1176</ID>

I want to write query that returns me a column as identiry. I tried using AUTHOR.query('data(/Author/ID)') as identity but it fails when XML does not have Author node.

Thanks, Vijay

Answer 1

Have you tried something like /Author/ID|/ID ? i.e. try for the first scenario, and with no match, the second ?

(note that the | operator is a set union operator, as described here)

Answer 2

In case that nothing "certain" can be maintained about the XML, except that a unique ID element contains the required identity value, then the following XPath expression selects the ID element:

//ID