>Hi All,
>
>I am working with the SQL tables that has XML data stored in a field defined as varchar. I am trying to extract specific element from the XML and this is what I have so far:
>
>
>select XMLInString, CAST(XMLInString as XML),
> CAST(XMLInString as XML).query('/pbXML/Shipment/BillNr[1]')
>from ShipmentPlanning
>
>
>This almost works. 3rd field in the above query returns: BillNr 9999 /BillNr
>
>What I am looking for is only the 9999 part to be returned.
>
>Thank you,
>daniel
You may want to post a sample XML. Also, once you extracted the above, you can easily get the number.
Check this blog post
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server
If it's not broken, fix it until it is.
My Blog