Solved

Using xml data type in a query

Posted on 2009-07-15
7
199 Views
Last Modified: 2012-05-07
I have a field in a table which is xml.

The xml embedded in the field ClientDetails as follows

Transaction>
    <Clients>
    <ClientDetails>
      <Title>Mrs.</Title>
      <FirstName>Jackie</FirstName>
      <MiddleNames />
      <Surname>Trial</Surname>
      <DateOfBirth />
    </ClientDetails>
    <ClientDetails>
      <Title>Mr.</Title>
      <FirstName>Ian</FirstName>
      <MiddleNames />
      <Surname>Test</Surname>
      <DateOfBirth />
    </ClientDetails>
    <ContactDetails>
      <AddressDetails>
        <AddressLine1>The Hollows</AddressLine1>
        <AddressLine2>2 Smith Lane</AddressLine2>
        <AddressLine3>Doncaster</AddressLine3>
        <AddressLine4>South Yorkshire</AddressLine4>
        <AddressLine5 />
        <Postcode>D3 5TY</Postcode>
        <Country />
      </AddressDetails>
      <EmailAddress>mark.smith@irwinmitchell.com</EmailAddress>
      <PhoneNumber />
      <MobileNumber />
    </ContactDetails>
  </Clients>
</Transaction>

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?

Thanks
0
Comment
Question by:halifaxman
  • 4
  • 2
7 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 24865062
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.
untitled.JPG
0
 

Author Comment

by:halifaxman
ID: 24867474
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24868647
what database?  The functions to manipulate xml vary by platform
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:halifaxman
ID: 24869223
sorry its a sql 2008 database
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24869229
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.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24869234
Just to confirm:  Sql Server, correct?
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24869242
"xmldata" was the name of the column in my particular example. You should change that to whatever the name of your XML column is.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now