SQL Server XML query: how to select values?

Question

How to select file names from xml like this, i.e. I need only names:

c:\temp\f1.txt
c:\temp\f2.txt

XML like this:

<root name="name" id="12">
<class1>
     <file name="c:\temp\f1.txt">
</class1>
<class1>
   <subclass1>
        <file name="c:\temp\f2.txt">
    </subclass1>
</class1>
</root>

Mikael Eriksson · Accepted Answer · 2012-11-05 17:54:11Z

2

declare @XML xml 
set @XML = '
<root name="name" id="12">
  <class1>
       <file name="c:\temp\f1.txt"/>
  </class1>
  <class1>
     <subclass1>
          <file name="c:\temp\f2.txt"/>
      </subclass1>
  </class1>
</root>'

select T.N.value('@name', 'nvarchar(100)') as FileName
from @XML.nodes('//file') as T(N)

answered Nov 5, 2012 at 17:54

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server XML query: how to select values?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related