XML datatype to columned dataset

[Daniel Bass]

Greetings!

Give a column of XML datatype containing this:

<Foo>
<Name>Bob</Name>
<Area>Ghana</Area>
<Interest>Polar Hiking</Interest>
</Foo>
How do I write SQL that will produce a data set (DYNAMICALLY)?

Name Area Interest
-----------------------------
Bob Ghana Polar Hiking
The idea is that the name of the elements in my XML are untyped, and may
change. But I'd like a dynamic query to handle this mapping.

Thanks for your time.
Dan.

[Martin Honnen]

Daniel Bass wrote:

Give a column of XML datatype containing this:

<Foo>
<Name>Bob</Name>
<Area>Ghana</Area>
<Interest>Polar Hiking</Interest>
</Foo>
How do I write SQL that will produce a data set (DYNAMICALLY)?

Name Area Interest
-----------------------------
Bob Ghana Polar Hiking
The idea is that the name of the elements in my XML are untyped, and may
change. But I'd like a dynamic query to handle this mapping.

The DataSet has a ReadXml method, if you simply read in that above XML
using that method then the DataSet instance has one table named Foo with
three columns Name, Area, and Interest.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

[Daniel Bass]

Thanks for your reply. I'm looking at the SQL side of things, and would like
this in a stored procedure, not in a .Net app...

Any ideas?
Cheers.
Dan.

"Martin Honnen" <ma*******@yahoo.dewrote in message
news:eF**************@TK2MSFTNGP02.phx.gbl...

Daniel Bass wrote:

>Give a column of XML datatype containing this:

<Foo>
<Name>Bob</Name>
<Area>Ghana</Area>
<Interest>Polar Hiking</Interest>
</Foo>
How do I write SQL that will produce a data set (DYNAMICALLY)?

Name Area Interest
-----------------------------
Bob Ghana Polar Hiking
The idea is that the name of the elements in my XML are untyped, and may
change. But I'd like a dynamic query to handle this mapping.

The DataSet has a ReadXml method, if you simply read in that above XML
using that method then the DataSet instance has one table named Foo with
three columns Name, Area, and Interest.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

[Alex J. Lee]

This is really interesting thins.
However, I don't understand what you Daniel wat to do.
Do you want to create table dynamically using xml you mentioned?
Or, do you just want to insert data from xml to already created table?

If possible, please give me more detail information.
I wanna try this one.

Alex

"Daniel Bass" <da***********@blueCAPSbottle.comFIRSTwrote in message
news:uO**************@TK2MSFTNGP04.phx.gbl...

Thanks for your reply. I'm looking at the SQL side of things, and would
like this in a stored procedure, not in a .Net app...

Any ideas?
Cheers.
Dan.

"Martin Honnen" <ma*******@yahoo.dewrote in message
news:eF**************@TK2MSFTNGP02.phx.gbl...

>Daniel Bass wrote:

>>Give a column of XML datatype containing this:

<Foo>
<Name>Bob</Name>
<Area>Ghana</Area>
<Interest>Polar Hiking</Interest>
</Foo>
How do I write SQL that will produce a data set (DYNAMICALLY)?

Name Area Interest
-----------------------------
Bob Ghana Polar Hiking
The idea is that the name of the elements in my XML are untyped, and may
change. But I'd like a dynamic query to handle this mapping.

The DataSet has a ReadXml method, if you simply read in that above XML
using that method then the DataSet instance has one table named Foo with
three columns Name, Area, and Interest.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/