How to query a xml data type in SQL?-CodePudding

I have this xml data in sql.

<Bakery xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Bakery">
  <name>My Bakery</name>
  <sandwiches>
    <Sandwich>
      <breadType>White</breadType>
      <ingredients>
        <Ingredient>
          <name>Cucumber</name>
          <price>0.05</price>
        </Ingredient>
        <Ingredient>
          <name>Tomato</name>
          <price>0.15</price>
        </Ingredient>
      </ingredients>
      <name>Chicken Sandwich</name>
      <price>0.25</price>
    </Sandwich>  
  </sandwiches>
</Bakery>

I tried to query the name of Bakery in the following way:

SELECT X.Y.value('(name)[1]', 'VARCHAR(100)') as 'Bakery Name' FROM BAKERY as b 
cross APPLY b.Bakery_record.nodes('Bakery') as X(Y)

But the result that I get is just an empty cell.

I also tried to query BreadType like the following:

SELECT X.Y.value('(breadType)[1]', 'VARCHAR(100)') as 'Bread Type' FROM BAKERY as b 
cross APPLY b.Bakery_record.nodes('Bakery/sandwiches/Sandwich') as X(Y)

But I also get an empty cell as a result.

What am I missing?

CodePudding user response：

You need to specify the namespace for the elements in your XPath expressions. In this case, the namespace is http://schemas.datacontract.org/2004/07/Bakery, so you can use the WITH XMLNAMESPACES clause in your query to define the namespace, like this:

WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/Bakery' AS ns)
SELECT X.Y.value('(ns:name)[1]', 'VARCHAR(100)') as 'Bakery Name' FROM BAKERY as b 
cross APPLY b.Bakery_record.nodes('ns:Bakery') as X(Y)

The same applies to the second query. You need to specify the namespace in the XPath expression, like this:

WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/Bakery' AS ns)
SELECT X.Y.value('(ns:breadType)[1]', 'VARCHAR(100)') as 'Bread Type' FROM BAKERY as b 
cross APPLY b.Bakery_record.nodes('ns:Bakery/ns:sandwiches/ns:Sandwich') as X(Y)

This should return the expected results.