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

[terrorix]

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 ?

 

[Nicholas Paldino [.NET/C# MVP]]

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]
- (e-mail address removed)

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 ?

 

[terrorix]

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 ?

 

[Nicholas Paldino [.NET/C# MVP]]

terrorix,

You need one more step:

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

That's all there is to it.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

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 ?

 

[terrorix]

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."

 

[Nicholas Paldino [.NET/C# MVP]]

terrorix,

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


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

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

Exception:
InvalidOperationException, message: "This document already has a

DocumentElement node."

 

[terrorix]

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>

 

[Nicholas Paldino [.NET/C# MVP]]

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]
- (e-mail address removed)

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>

 

[terrorix]

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...