Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
1 reply Latest Post - ‏2011-10-26T06:45:20Z by SystemAdmin
sree.koneru
sree.koneru
1 Post
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: Xpath for reading data...

    ‏2011-10-26T06:45:20Z  in response to sree.koneru
    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