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 nice 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>

Answers


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)

Need Your Help

Create delegates for a NSOperation : callback problem

objective-c delegates nsoperation

I'm facing a problem in a simple TableView : i want to refresh the data from the Net in a separate thread to avoid blocking the UI. I've created a NSoperation that will do the job. This object crea...

Modified PHP PDO Login script still doesn't work

php sql pdo prepared-statement

I'm learning php and OOP programming here. I have below working code I am trying to modify to prevent sql injection. Other people showed me the idea of how to use PDO. But I'm having difficulty get...