"Pretty" indentation of output using SQLXML managed classes?

[pmarfleet]

I am using the SQLXML managed classes to generate some XML from a SQL Server database, specifying a reference to an XSL stylesheet for my SqlXmlCommand object to transform the raw XML returned from the database. My stylesheet is as follows: I have added the attribute indent="yes" to my element which has the effect of formatting the output with line feeds. This is fine, however I would like to "prettily" indent the output in a similar manner to that which can be produced using the XmlTextWriter. What is the best way of achieving this? Can I modify my stylesheet to indent the nodes? Or would I have to perform some post-processing of the output in my .NET code to achieve this, perhaps using a combination of an XmlTextReader and XmlTextWriter? Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush -- modified at 18:05 Thursday 1st November, 2007

[Stefan Troschuetz]

pmarfleet wrote:

Can I modify my stylesheet to indent the nodes?

Yes and no. To indent the XML output you can assign yes to the indent attribute of the output element but the XSLT standard does not define that a processor has to adhere and how the XML output has to be indented. (XSLT: "If the indent attribute has the value yes, then the xml output method may output whitespace in addition to the whitespace in the result tree"). Probably the XML processor of the SQLXML processor does ignore this attribute or maybe you have to tell him explicitly which number of whitespaces he should use for indentation. Personally I never used those classes so I cannot tell what to do. Have a look if there might be a setting for this. Also it could be helpful if you post the code you've got so far, making it easier for us to help you.

---

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rick Cook

www.troschuetz.de

[pmarfleet]

My .NET code is as follows:

public static string ExecuteQueryAndTransform(string connectionString, string queryText, string xslPath)
{
string result = String.Empty;

        SqlXmlCommand cmd = new SqlXmlCommand(connectionString);
        cmd.XslPath = xslPath;
        cmd.RootTag = "Root";
        cmd.CommandText = queryText;
        
        using (Stream strm = cmd.ExecuteStream())
        {
            using (StreamReader sr = new StreamReader(strm))
            {
                result = sr.ReadToEnd();
            }
        }

        return result;

    }

There doesn't appear to be any way to control the indenting from the SQLXML managed classes, so I assume this would be done through the stylesheet.

Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush

[pmarfleet]

I found the answer to my problem. If I perform the transform using the .NET framework classes instead of the SQLXML managed classes, I can use the XmlTextWriter to control the indentation.

public static string ExecuteQueryAndTransform(string connectionString, string queryText, string xslPath)
{
string result = String.Empty;
SqlXmlCommand cmd = new SqlXmlCommand(connectionString);

        if (!queryText.EndsWith("For XML Auto", StringComparison.CurrentCultureIgnoreCase))
        {
            queryText += " For XML Auto";
        }            
        cmd.RootTag = "Root";
        cmd.CommandText = queryText;
        
        using (Stream strm = cmd.ExecuteStream())
        {
            XmlTextReader reader = new XmlTextReader(strm);
            XPathDocument xd = new XPathDocument(reader, XmlSpace.Preserve);
            XslCompiledTransform xslt = new XslCompiledTransform();
            xslt.Load(xslPath);
            StringWriter stringWriter = new StringWriter();
            XmlTextWriter writer = new XmlTextWriter(stringWriter);
            writer.Formatting = Formatting.Indented;
            writer.Indentation = 3;
            writer.IndentChar = ' ';
            xslt.Transform(xd, null, writer);
            result = stringWriter.ToString();
        }
 }

Thanks for your help anyway.

Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush