Convert XmlReader to string

[muesliflakes]

I wish to receive some data from SqlServer as XML and write out result
to as a string.

Eg: cmd = new SqlCommand( "select * from blah for xml auto", con );

XmlReader result = cmd.ExecuteXmlReader( );

Console.Writeln( result.ToString( ) );

This XML is meant to be the input for an XSLT transform so I'm
interested in seeing the XML in a form similar to <row col1='blah'
col2='blah' />. The XML comes back ok, but I'm not sure how to convert
the XmlReader into a String in an efficient manner.

One way I know would be to load it into a dataset and then write out
as XML and another way would be to serialize / deserialize from an
object, but surely there is a simple way of converting the XmlReader
into a String.

Cheers Dave

 

[Jon Skeet [C# MVP]]

I wish to receive some data from SqlServer as XML and write out result
to as a string.

Eg: cmd = new SqlCommand( "select * from blah for xml auto", con );

XmlReader result = cmd.ExecuteXmlReader( );

Console.Writeln( result.ToString( ) );

This XML is meant to be the input for an XSLT transform so I'm
interested in seeing the XML in a form similar to <row col1='blah'
col2='blah' />. The XML comes back ok, but I'm not sure how to convert
the XmlReader into a String in an efficient manner.

One way I know would be to load it into a dataset and then write out
as XML and another way would be to serialize / deserialize from an
object, but surely there is a simple way of converting the XmlReader
into a String.

The easiest thing is to read the whole document in using
XmlDocument.Load(XmlReader) and then either write it out again using an
XmlWriter writing to a StringWriter, or something similar. (I haven't
tried calling OuterXml on an XmlDocument - that might do it for you in
a very straightforward way.)

Of course, this is a somewhat inefficient way of doing things. An
alternative is to have an XmlWriter writing to a StringWriter and use
XmlWriter.WriteNode (XmlReader, bool). I can't say I've tried that
either, to be honest - but it's worth having a go.

 

[d c]

I would love to use the XmlDocument class but the problem is that this
class requires the XmlReader to return a well formed document.

My query which is to get the structure of a table:

select * from information_schema.columns where table_name = 'MtFeedback'
for xml auto

Does not return a well formed document, it only returns an XML fragment.

No Root Element:
<information_schema.columns TABLE_CATALOG="Mt" TABLE_SCHEMA="dbo"
TABLE_NAME="MtFeedback" COLUMN_NAME="MtFeedbackIy" ORDINAL_POSITION="1"
IS_NULLABLE="No " DATA_TYPE="int" NUMERIC_PRECISION="10"
NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0"/>

<information_schema.columns TABLE_CATALOG="Mt" TABLE_SCHEMA="dbo"
TABLE_NAME="MtFeedback" COLUMN_NAME="Title" ORDINAL_POSITION="2"
IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50"
CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1"
COLLATION_NAME="Latin1_General_CI_AS"/>

etc...