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

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.