#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    136
    Rep Power
    10

    Querying xml that has a xmlns namespace (beginner)


    Hello,

    I'm trying to figure out how to access XML using sql queries.

    I have 2 pieces of code which is identical, however 1 is using a xmlns namespace, the other one is not.

    How would I change the select query in the first example to make it work(Given that there is such an XMLSchema)?

    Thanks

    Example 1 - this one does not work:
    Code:
    declare @myDoc xml
    set @myDoc = '<Root xmlns="http://www.somewebsite.com/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.somewebsite.com/XMLSchema/someXmlSchema.xsd">>
    <ProductDescription ProductID="1" ProductName="Road Bike">
    <Features>
      <Warranty>1 year parts and labor</Warranty>
      <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
    </Features>
    </ProductDescription>
    </Root>'
    SELECT @myDoc.query('/Root/ProductDescription/Features')

    example 2 - this one works as expected:

    Code:
    declare @myDoc xml
    set @myDoc = '<Root>
    <ProductDescription ProductID="1" ProductName="Road Bike">
    <Features>
      <Warranty>1 year parts and labor</Warranty>
      <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
    </Features>
    </ProductDescription>
    </Root>'
    SELECT @myDoc.query('/Root/ProductDescription/Features')
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,997
    Rep Power
    9397
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    136
    Rep Power
    10
    Originally Posted by requinix
    What database system is this in?
    It's SQL Server 2008.

    As I understand it the value inside @myDoc.query('/Root/ProductDescription/Features') part is the XPath address so the problem should be the XML(presumably the XPath) and not the SQL

    It seems like it fails because I added a xmlns="..." xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="....", because it now has a namespace I assume I have to edit the XPath to make it correct? but how?
    Last edited by Dag; August 1st, 2012 at 02:07 PM.

IMN logo majestic logo threadwatch logo seochat tools logo