Safely retrieve values from element that can contain different values

We have some xml elements in a database that [for older data] can sometimes contain guids and sometimes contain integers.

is there a ni开发者_运维问答ce way of pulling out all the integrs only?

This will fail if the value element contains a guid!

select
ra.*,
t.c.value('.', 'int') as organisationId
from 
   Audit.EmployeeAudit ra
   cross apply ra.EmployeeXml.nodes('//*:employee/*:property[*:name="ORG"]/*:value') t(c)

Sample Xml

<employee>
  <property>
    <name>ORG</name>
    <value>39</value> <!-- Sometimes this will be a guid -->
    <description>Leeds</description>
  </property>
</employee>

You could add a predicate to only match entries of less than or equal to 10 characters.

;with EmployeeAudit as
(

SELECT CAST('<employee><property>
   <name>ORG</name>
   <value>39</value> <!-- Sometimes this will be a guid -->
   <description>Leeds</description>
</property></employee>
' AS XML) AS EmployeeXml
UNION ALL
SELECT CAST('<employee><property>
   <name>ORG</name>
   <value>2FD29F11-59FC-47FD-BC30-DD330A53284E</value> 
   <description>Leeds</description>
</property></employee>
' AS XML)
)
select
ra.*,
t.c.value('.', 'int') as organisationId
from 
   EmployeeAudit ra
   cross apply 
   ra.EmployeeXml.nodes
   ('//*:employee/*:property[*:name="ORG"]/*:value[string-length() <= 10]') t(c)

Or actually this might be a bit more robust

('//*:employee/*:property[*:name="ORG"]/*:value[ceiling(.) = .]') t(c)