Using xml data type in a query

I have a field in a table which is xml.

The xml embedded in the field ClientDetails as follows

      <MiddleNames />
      <DateOfBirth />
      <MiddleNames />
      <DateOfBirth />
        <AddressLine1>The Hollows</AddressLine1>
        <AddressLine2>2 Smith Lane</AddressLine2>
        <AddressLine4>South Yorkshire</AddressLine4>
        <AddressLine5 />
        <Postcode>D3 5TY</Postcode>
        <Country />
      <PhoneNumber />
      <MobileNumber />

I am looking to extract some of the information in the xml to use in a select criteria, but I am not sure how to do it.

i.e. Select Firstname, Surname
from table

Can anybody help?

Who is Participating?
käµfm³d 👽Connect With a Mentor Commented:
There are a couple of xml functions you can use to extract data. I am attaching a screen shot of the functions as well as what they returned using your data.

If for some reason the image is not good, I can post the code. I just wanted to demonstrate the code and results together.
halifaxmanAuthor Commented:
Thanks for the answer

I have used the following

SELECT xmldata.query('(/Transaction/Clients/ClientDetails[1]/Firstname/text())') as FN
from xmltest

I get the error message

Cannot find either column "xmldata" or the user-defined function or aggregate "xmldata.query", or the name is ambiguous
what database?  The functions to manipulate xml vary by platform
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

halifaxmanAuthor Commented:
sorry its a sql 2008 database
käµfm³d 👽Commented:
I guess I jumped the gun :)  I get so used to working in MS stuff I sometimes forget to look at the zone!! Can you answer sdstuber's question in order that the answer may be revised.
käµfm³d 👽Commented:
Just to confirm:  Sql Server, correct?
käµfm³d 👽Commented:
"xmldata" was the name of the column in my particular example. You should change that to whatever the name of your XML column is.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.