469,645 Members | 1,197 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

How i can fill an XmlDocument object with this SQL select using XmlReader, SqlCommand ?

I also posted this article at: microsoft.public.dotnet.framework.aspnet
--------
Hi,

I Have this sql select(MS SQL):

select a.ID,
b.ID
from Table as a

left outer
join table AS b
on a.ID = b.Parent_ID

where a.Parent_ID is null

order
by a.ID
, b.ID

for xml auto, xmldata

How i can fill an XmlDocument object with this SQL select using XmlReader, SqlCommand ?
Nov 16 '05 #1
8 2354
terrorix,

What you want to do is call the ExecuteXmlReader method on the
SqlCommand instance representing your query. Doing this will allow you to
get an XmlReader which you can do anything you wish with it.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"terrorix" <te******@centrum.sk> wrote in message
news:uI***************@TK2MSFTNGP12.phx.gbl...
I also posted this article at: microsoft.public.dotnet.framework.aspnet
--------
Hi,

I Have this sql select(MS SQL):

select a.ID,
b.ID
from Table as a

left outer
join table AS b
on a.ID = b.Parent_ID

where a.Parent_ID is null

order
by a.ID
, b.ID

for xml auto, xmldata

How i can fill an XmlDocument object with this SQL select using XmlReader,

SqlCommand ?
Nov 16 '05 #2
Nicholas Paldino [.NET/C# MVP] wrote:
terrorix,

What you want to do is call the ExecuteXmlReader method on the
SqlCommand instance representing your query. Doing this will allow you to
get an XmlReader which you can do anything you wish with it.

Hope this helps.


I know this i can use that:

SqlCommand SqlCommand1 = new SqlCommand("select * from table for xml auto, xmldata");
XmlReader reader = SqlCommand1.ExecuteXmlReader();

XmlDocument doc = new XmlDocument();

but what else i must do to fill doc(XmlDocument) object with data from returned sql select ?
Nov 16 '05 #3
terrorix,

You need one more step:

// Load the document.
doc.Load(reader);

That's all there is to it.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"terrorix" <te******@centrum.sk> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
Nicholas Paldino [.NET/C# MVP] wrote:
terrorix,

What you want to do is call the ExecuteXmlReader method on the
SqlCommand instance representing your query. Doing this will allow you to get an XmlReader which you can do anything you wish with it.

Hope this helps.
I know this i can use that:

SqlCommand SqlCommand1 = new SqlCommand("select * from table for xml

auto, xmldata"); XmlReader reader = SqlCommand1.ExecuteXmlReader();

XmlDocument doc = new XmlDocument();

but what else i must do to fill doc(XmlDocument) object with data from

returned sql select ?
Nov 16 '05 #4
Nicholas Paldino [.NET/C# MVP] wrote:
terrorix,

You need one more step:

// Load the document.
doc.Load(reader);

That's all there is to it.


that doesnt not function, this throws me an exception after line doc.Load(reader);

Exception:
InvalidOperationException, message: "This document already has a DocumentElement node."
Nov 16 '05 #5
terrorix,

What does the XML that SQL server is returning look like?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"terrorix" <te******@centrum.sk> wrote in message
news:%2***************@TK2MSFTNGP12.phx.gbl...
Nicholas Paldino [.NET/C# MVP] wrote:
terrorix,

You need one more step:

// Load the document.
doc.Load(reader);

That's all there is to it.
that doesnt not function, this throws me an exception after line

doc.Load(reader);
Exception:
InvalidOperationException, message: "This document already has a

DocumentElement node."
Nov 16 '05 #6
Nicholas Paldino [.NET/C# MVP] wrote:
terrorix,

What does the XML that SQL server is returning look like?


<p parent="A - Hlavna kategoria 1">
<c child="Podkategoria A [level1]">
<g grandchild="Podkategoria A [level2]"/>
</c>
<c child="Podkategoria A2 [level1]">
<g/>
</c>
</p>
<p parent="B - Hlavna kategoria 2">
<c child="Podkategoria B [level1]">
<g/>
</c>
</p>
Nov 16 '05 #7
This makes sense now. There is no document root for this XML. When you
pass this to the XmlDocument, it tries to read the whole stream, and because
there is no root, it bombs.

If you want to have all the <p> elements in the document, then you will
have to modify this XML so that it has a root element.

If you want to read one at a time, then you can call the Read method on
the XmlReader before you pass it to the Load method of the XmlDocument.
This will move the pointer in the reader to the first P element. The
XmlDocument will detect that there is a current node, and only read the
contents of that node into the document.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"terrorix" <te******@centrum.sk> wrote in message
news:O7**************@TK2MSFTNGP12.phx.gbl...
Nicholas Paldino [.NET/C# MVP] wrote:
terrorix,

What does the XML that SQL server is returning look like?


<p parent="A - Hlavna kategoria 1">
<c child="Podkategoria A [level1]">
<g grandchild="Podkategoria A [level2]"/>
</c>
<c child="Podkategoria A2 [level1]">
<g/>
</c>
</p>
<p parent="B - Hlavna kategoria 2">
<c child="Podkategoria B [level1]">
<g/>
</c>
</p>

Nov 16 '05 #8
Nicholas Paldino [.NET/C# MVP] wrote:
This makes sense now. There is no document root for this XML. When you
pass this to the XmlDocument, it tries to read the whole stream, and because
there is no root, it bombs.

If you want to have all the <p> elements in the document, then you will
have to modify this XML so that it has a root element.

If you want to read one at a time, then you can call the Read method on
the XmlReader before you pass it to the Load method of the XmlDocument.
This will move the pointer in the reader to the first P element. The
XmlDocument will detect that there is a current node, and only read the
contents of that node into the document.

Hope this helps.


Thanx, i'll try it...
Nov 16 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Ayende Rahien | last post: by
1 post views Thread by Martin Honnen | last post: by
2 posts views Thread by Graham Pengelly | last post: by
2 posts views Thread by Glenn Venzke | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.