xml carriage return

6 Feb 2007 9:26 PM

Chuck P

I am doing an insert using parameters from vs05 to sql05
The data is going into an XML column.

SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "web.SurveyProfiles_upd";
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter p10 = new SqlParameter("@SurveyQuestions",
SqlDbType.Xml);
        p10.Value = obj.xmlStringToInsert;
        p10.Direction = ParameterDirection.Input;
        cmd.Parameters.Add(p10);

When I get this data back my nicely formatted XML is missing the carriage
returns after all the closing tags (I get one long string). Is their a way I
can get my raw XML back so that it is readable.

p.s. I am not talking about CRs within element data. These are just CRs
used so you can read the XML.

7 Feb 2007 6:42 AM

Charles Wang[MSFT]

Hi Chunk,
Did you mean that you wanted to get indented XML content like this:
<root>
    <node id="1" value="test1" />
    <node id="2" value="test2" />
</root>
rather than a long string "<root><node id="1" value="test1" /><node id="2"
value="test2" /></root>" ?

If so, I think that I reproduced your issue.
First, I inserted a xml string into the xml field:
private void btnAdd_Click(object sender, EventArgs e)
        {
            SqlConnection cn = new
SqlConnection("server=sha-chlwang-2k3\\wow;database=TestNull;integrated
security=SSPI");
            SqlCommand cmd = new SqlCommand("proc_insTestXML", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter pm10 = new SqlParameter("@xml",SqlDbType.Xml);
            pm10.Value = GetXmlContent();
            cmd.Parameters.Add(pm10);
            cn.Open();
            try
            {
                cmd.ExecuteNonQuery();
                MessageBox.Show("Successfully Inserted!");
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally {
                cn.Close();
            }
        }

       private string GetXmlContent()
        {
            StreamReader sr = new
StreamReader(m_filename,Encoding.UTF8,false,512);
            string strContent = sr.ReadToEnd();
            sr.Close();
            return strContent;
        }

Then, I tried to browse the XML content in a RichTextBox:
private void btnBrowseXML_Click(object sender, EventArgs e)
        {
            string strXml="";
            SqlConnection cn = new
SqlConnection("server=sha-chlwang-2k3\\wow;database=TestNull;integrated
security=SSPI");
            SqlCommand cmd = new SqlCommand("select * from TestXML WHERE ID
= (SELECT MAX(ID) FROM TestXML)", cn);
            cn.Open();
            try
            {
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.Read())
                {
                    strXml = rd[1].ToString();
                }
                rd.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                cn.Close();
            }
             this.txtContent.Text = strXml;
        }

I saw that the xml content was not indented but a long compact string.

However that does not matter, because we can convert the long compact
string to a XML indented string by this method:
private String GetIndentedXML(string strXml)
        {
            string strRet = null;
            if (strXml != null && strXml.Length>0)
            {
                XmlDocument xdoc = new XmlDocument();
                xdoc.LoadXml(strXml);
                MemoryStream ms = new MemoryStream();
                XmlTextWriter xtw = new XmlTextWriter(ms, Encoding.UTF8);
                xtw.Formatting = Formatting.Indented;
                xdoc.Save(xtw);
                ms.Position = 0;
                StreamReader rd1 = new StreamReader(ms, Encoding.UTF8);
                strRet = rd1.ReadToEnd();
    ms.Close();
                xtw.Close();
                rd1.Close();
            }
            return strRet;
        }

Now if I use "this.txtContent.Text = GetIndentedXML(strXml)" to display the
XML field value, the content is indented.

Hope this helps. If I have misunderstood your issue, I would like that you
describe me your issue more detailed so that I can reproduce your issue and
find a resolution.

Please feel free to let me know if you have any other questions or concerns.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

7 Feb 2007 11:25 AM

Michael Rys [MSFT]

In addition, if you need to preserve the whitespace in the XML datatype
because you want to query it, pass it as varbinary to the server and use the
CONVERT option 1 to cast it to XML with whitespace preservation... Also
please file a request at http://connect.microsoft.com/sqlserver to add an
option on the provider side to preserve whitespace.

Best regards
Michael

Show quote

"Charles Wang[MSFT]" <chang***@online.microsoft.com> wrote in message
news:n29AgMoSHHA.3604@TK2MSFTNGHUB02.phx.gbl...
> Hi Chunk,
> Did you mean that you wanted to get indented XML content like this:
> <root>
> <node id="1" value="test1" />
> <node id="2" value="test2" />
> </root>
> rather than a long string "<root><node id="1" value="test1" /><node id="2"
> value="test2" /></root>" ?
>
> If so, I think that I reproduced your issue.
> First, I inserted a xml string into the xml field:
> private void btnAdd_Click(object sender, EventArgs e)
>        {
>            SqlConnection cn = new
> SqlConnection("server=sha-chlwang-2k3\\wow;database=TestNull;integrated
> security=SSPI");
>            SqlCommand cmd = new SqlCommand("proc_insTestXML", cn);
>            cmd.CommandType = CommandType.StoredProcedure;
>            SqlParameter pm10 = new SqlParameter("@xml",SqlDbType.Xml);
>            pm10.Value = GetXmlContent();
>            cmd.Parameters.Add(pm10);
>            cn.Open();
>            try
>            {
>                cmd.ExecuteNonQuery();
>                MessageBox.Show("Successfully Inserted!");
>            }
>            catch (SqlException ex)
>            {
>                MessageBox.Show(ex.Message);
>            }
>            finally {
>                cn.Close();
>            }
>        }
>
>       private string GetXmlContent()
>        {
>            StreamReader sr = new
> StreamReader(m_filename,Encoding.UTF8,false,512);
>            string strContent = sr.ReadToEnd();
>            sr.Close();
>            return strContent;
>        }
>
> Then, I tried to browse the XML content in a RichTextBox:
> private void btnBrowseXML_Click(object sender, EventArgs e)
>        {
>            string strXml="";
>            SqlConnection cn = new
> SqlConnection("server=sha-chlwang-2k3\\wow;database=TestNull;integrated
> security=SSPI");
>            SqlCommand cmd = new SqlCommand("select * from TestXML WHERE ID
> = (SELECT MAX(ID) FROM TestXML)", cn);
>            cn.Open();
>            try
>            {
>                SqlDataReader rd = cmd.ExecuteReader();
>                if (rd.Read())
>                {
>                    strXml = rd[1].ToString();
>                }
>                rd.Close();
>            }
>            catch (SqlException ex)
>            {
>                MessageBox.Show(ex.Message);
>            }
>            finally
>            {
>                cn.Close();
>            }
>             this.txtContent.Text = strXml;
>        }
>
> I saw that the xml content was not indented but a long compact string.
>
> However that does not matter, because we can convert the long compact
> string to a XML indented string by this method:
> private String GetIndentedXML(string strXml)
>        {
>            string strRet = null;
>            if (strXml != null && strXml.Length>0)
>            {
>                XmlDocument xdoc = new XmlDocument();
>                xdoc.LoadXml(strXml);
>                MemoryStream ms = new MemoryStream();
>                XmlTextWriter xtw = new XmlTextWriter(ms, Encoding.UTF8);
>                xtw.Formatting = Formatting.Indented;
>                xdoc.Save(xtw);
>                ms.Position = 0;
>                StreamReader rd1 = new StreamReader(ms, Encoding.UTF8);
>                strRet = rd1.ReadToEnd();
> ms.Close();
>                xtw.Close();
>                rd1.Close();
>            }
>            return strRet;
>        }
>
> Now if I use "this.txtContent.Text = GetIndentedXML(strXml)" to display
> the
> XML field value, the content is indented.
>
> Hope this helps. If I have misunderstood your issue, I would like that
> you
> describe me your issue more detailed so that I can reproduce your issue
> and
> find a resolution.
>
> Please feel free to let me know if you have any other questions or
> concerns.
>
> Sincerely yours,
> Charles Wang
> Microsoft Online Community Support
>
> ======================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================
>

7 Feb 2007 3:22 PM

Chuck P

thanks, never thought of doing that!

The writer did put in a declaration artificat, so I modified the code a
little to omit the declaration.

   public static String GetIndentedXML(string strXml)
        {
            string strRet = null;

            if (strXml != null && strXml.Length > 0)
            {
                XmlWriterSettings settings = new XmlWriterSettings();
                settings.Indent = true;
                settings.OmitXmlDeclaration = true;

                XmlDocument xdoc = new XmlDocument();

                xdoc.LoadXml(strXml);
                using (MemoryStream ms = new MemoryStream())
                {
                    using (XmlWriter xtw = XmlWriter.Create(ms, settings))
                    {
                        xdoc.Save(xtw);
                        ms.Position = 0;
                        using (StreamReader sr = new StreamReader(ms,
Encoding.UTF8))
                        {
                            strRet = sr.ReadToEnd();
                        }
                    }
                }

            }
            return strRet;
        }

8 Feb 2007 6:43 AM

Charles Wang[MSFT]

Hi Chuck,
Thanks for your updating and response.

I am very glad to hear that the suggestions are helpful to you and you have
improved the method to suit your situation. If you have any other questions
or concerns, please feel free to let me know. It is my pleasure to be of
assistance.

Have a great day!

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

31 Oct 2007 10:25 PM

Ed Shnekendorf

I had a similar issue except all my processing was being done on the server
so I didn't have the luxury of using C# methods.

I ended up doing something like this:

DECLARE @msg NVARCHAR(max)
SET @msg = CONVERT(nvarchar(max),
(SELECT ... FOR XML AUTO) )

SELECT REPLACE(@msg, '</tagname>', '</tagname>' + CHAR(13) + CHAR(10))

This method isn't the most flexible since you have to hardcode the tagname
that you want line breaks after, but if the intent is to allow a program like
Excel (which won't be able to read a really long continuous line) to read
your results and you are not going through the client layer (perhaps sending
the xml file as an attachment using DB Mail) then this does the trick.

Show quote

"Chuck P" wrote:

> I am doing an insert using parameters from vs05 to sql05
> The data is going into an XML column.
>
>   SqlCommand cmd = new SqlCommand();
>         cmd.CommandText = "web.SurveyProfiles_upd";
>         cmd.CommandType = CommandType.StoredProcedure;
>
>         SqlParameter p10 = new SqlParameter("@SurveyQuestions",
> SqlDbType.Xml);
>         p10.Value = obj.xmlStringToInsert;
>         p10.Direction = ParameterDirection.Input;
>         cmd.Parameters.Add(p10);
>
> When I get this data back my nicely formatted XML is missing the carriage
> returns after all the closing tags (I get one long string). Is their a way I
> can get my raw XML back so that it is readable.
>
> p.s. I am not talking about CRs within element data. These are just CRs
> used so you can read the XML.

31 Oct 2007 11:39 PM

Mike C#

You might be able to achieve something similar with the CONVERT function and
style 1 or 3 that preserves white space. It still appears to get rid of
carriage returns but leaves linefeeds (char(10)) in place.

DECLARE @x xml;
SET @x = CONVERT(xml, N'<root>
<child>test</child>
<child>test2</child>
</root>' , 1);
SELECT @x, ASCII(SUBSTRING(CAST(@x AS varchar(max)), 7, 1));
-- returns 10

Show quote

"Ed Shnekendorf" <EdShnekend***@discussions.microsoft.com> wrote in message
news:C3F7781A-29FB-45A3-987B-3DC33D60360B@microsoft.com...
>I had a similar issue except all my processing was being done on the server
> so I didn't have the luxury of using C# methods.
>
> I ended up doing something like this:
>
> DECLARE @msg NVARCHAR(max)
> SET @msg = CONVERT(nvarchar(max),
> (SELECT ... FOR XML AUTO) )
>
> SELECT REPLACE(@msg, '</tagname>', '</tagname>' + CHAR(13) + CHAR(10))
>
> This method isn't the most flexible since you have to hardcode the tagname
> that you want line breaks after, but if the intent is to allow a program
> like
> Excel (which won't be able to read a really long continuous line) to read
> your results and you are not going through the client layer (perhaps
> sending
> the xml file as an attachment using DB Mail) then this does the trick.
>
> "Chuck P" wrote:
>
>> I am doing an insert using parameters from vs05 to sql05
>> The data is going into an XML column.
>>
>>   SqlCommand cmd = new SqlCommand();
>>         cmd.CommandText = "web.SurveyProfiles_upd";
>>         cmd.CommandType = CommandType.StoredProcedure;
>>
>>         SqlParameter p10 = new SqlParameter("@SurveyQuestions",
>> SqlDbType.Xml);
>>         p10.Value = obj.xmlStringToInsert;
>>         p10.Direction = ParameterDirection.Input;
>>         cmd.Parameters.Add(p10);
>>
>> When I get this data back my nicely formatted XML is missing the carriage
>> returns after all the closing tags (I get one long string). Is their a
>> way I
>> can get my raw XML back so that it is readable.
>>
>> p.s. I am not talking about CRs within element data. These are just CRs
>> used so you can read the XML.