Topic
  • 1 reply
  • Latest Post - ‏2011-10-26T06:45:20Z by SystemAdmin
sree.koneru
sree.koneru
1 Post

Pinned topic Xpath for reading data...

‏2011-10-25T21:16:05Z |
I have a resource table which has an XMLPropertyBag as an XML column...which has the below format.I want to Query the EndDate for all resources.

<Resource xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/testdb.Data.DataContexts">
<City />
<CostCenter>2178040102</CostCenter>
<EndDate>2011-12-11T00:00:00</EndDate>
<JobArea>30000007</JobArea>
<JobFamily>30363568</JobFamily>
<JobID>30363569</JobID>
<JobTitle>Services Engineer</JobTitle>
<StartDate>2011-03-07T00:00:00</StartDate>
<YearsOfExperience>0</YearsOfExperience>
</Resource>

I used the below query..but not getting the values..getting all empty records.

SELECT XMLPropertyBag.query('/Resource/EndDate') from dbo.Resources

Please let me know if anyone has a clue.I am using SqlServer 2008.

Thanks a lot..
Updated on 2011-10-26T06:45:20Z at 2011-10-26T06:45:20Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1391 Posts

    Re: Xpath for reading data...

    ‏2011-10-26T06:45:20Z  
    That is sort of faq, that you've to understand the meaning of namespace qualified name and default namespace...

    The query should look something like this.
    
    SELECT XMLPropertyBag.query(
    ' declare 
    
    default element namespace 
    "http://schemas.datacontract.org/2004/07/testdb.Data.DataContexts"; data(/Resource/EndDate) 
    ') as EndDate  from dbo.Resources
    

    Or, for illustration using namespace prefix.
    
    SELECT XMLPropertyBag.query(
    ' declare namespace ns1=
    "http://schemas.datacontract.org/2004/07/testdb.Data.DataContexts"; data(/ns1:Resource/ns1:EndDate) 
    ') as EndDate  from dbo.Resources