How to select data from xml by T-SQL?

Question

I have the table: TestXml with 2 columns(pk , xCol). This is my table:

    create table TestXml (
pk int primary key, xCol xml)
on [Primary]
insert into Store.dbo.TestXml(pk,xCol) values (1,'<docs><doc id="12"><section> Section 1</section></doc>
<doc id="123"><section> Section 1</section>
<section> Section 2</section>
</doc>
</docs>'
)

the xml value , that I was insert looks like this:

   <docs>
  <doc id="12">
    <section> Section 1</section>
  </doc>
  <doc id="123">
    <section> Section 1</section>
    <section> Section 2</section>
  </doc>
</docs>

How can I take "<section>" element where "<doc id = "123">" ?

mwigdahl · Accepted Answer · 2011-12-29 19:07:08Z

3

Try this:

SELECT c.d.value('(.)[1]', 'varchar (100)') AS Section
FROM Store.dbo.TestXml tx
    CROSS APPLY tx.xCol.nodes('./docs/doc') AS a(b)
    CROSS APPLY a.b.nodes('./section') AS c(d)
WHERE a.b.value('(@id)[1]', 'int') = 123

answered Dec 29, 2011 at 19:07

mwigdahl

16.6k7 gold badges52 silver badges64 bronze badges

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

1 Comment

G21 Over a year ago

Thank you @mwigdahl for your example. This reasoning clears out my issue on how to dinamically read xml data types on SQL Server.

Collectives™ on Stack Overflow

How to select data from xml by T-SQL?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related