CODEWITHSUNDEEP
sqlsql-serverxmlxquery-sql
Oren A
Oren A

Reputation: 5890

Trying to get all xml nodes with SQL Server, what am I doing wrong?

I have the following xml in a table cell, in column MyColumn: 

<BSDL xmlns="..." xmlns:i="...">
  <dateTime>2012-12-30T00:00:00Z</dateTime>
  <dateTime>2013-01-07T00:00:00Z</dateTime>
  <dateTime>2013-01-14T00:00:00Z</dateTime>
  <dateTime>2013-01-21T00:00:00Z</dateTime>
  <dateTime>2013-01-29T00:00:00Z</dateTime>
  <dateTime>2013-02-05T00:00:00Z</dateTime>
  <dateTime>2013-02-12T00:00:00Z</dateTime>
  <dateTime>2013-02-19T00:00:00Z</dateTime>
  <dateTime>2013-03-22T00:00:00Z</dateTime>
  <dateTime>2013-03-29T00:00:00Z</dateTime>
  <dateTime>2013-04-19T00:00:00Z</dateTime>
</BSDL>

I'm just trying to query it (get all xml nodes) using: 

SELECT BSDL.item.value('(dateTime)[1]', 'datetime')
    from [MyTable]
    CROSS APPLY [MyColumn].nodes ('//BSDL') BSDL(item)

it yields no result, although my MyColumn in MyTable has the same number of entries as above, for each row.

Upvotes: 1

Views: 120

Answers (1)

marc_s
marc_s

Reputation: 754963

Since there's only one <BSDL> node - your call to //BSDL selects that single node and then item.value('(dateTime)[1]', 'datetime') returns the first <dateTime> child.

You need to change your XQuery to (use that xmlns=.... namespace you mention in your sample here):

;WITH XMLNAMESPACES('.....' as ns)
SELECT 
    item.value('.', 'datetime')
from 
    dbo.MyTable
CROSS APPLY 
    MyColumn.nodes ('/ns:BSDL/ns:dateTime') BSDL(item)

You need to get a list of all <dateTime> nodes under <BSDL> - then you'll get all of them and you can inspect them one by one.

Upvotes: 1

Related Questions