CODEWITHSUNDEEP
.netxmlt-sql
paparazzo
paparazzo

Reputation: 45096

Generate XML From SQL

I suspect this answer is in SO but I cannot find it. Some reports I generate from SQL 2008 R2 in a WPF application I would like to export to XML. The most common use of the XML is to import into Excel. Using TSQL and for xml auto I was able to to generate some rows and then manually edit in the header and root to get a valid XML document. My question is how do I generate a valid XML file via C# .NET 4.0 WPF. Do I just query using FOR XML AUTO then use XmlReader to iterate through the rows and write to file? How do I get a valid first line, root, and closing tags?

From what have read for forward only processing XMLreader and XMLwriter are faster than LINQ XML. XMLwriter also has the option of writing directly to disk as I may need to write out up to 1,000,000 lines of XML. My thought for now is to read the data with a SQLdataReader and write with XMLwriter. Does anyone think there is a faster way?

Code posted for John Sanders as he down voted me for giving no effort. John later removed the down vote and provided the accepted answer to my question. 

        XDocument xDoc = new XDocument(
            new XDeclaration("1.0","utf-8", "yes") 
                , new XComment("Gabriel Extract")
                //, new XElement("Documents", new XElement("sDoc", new XAttribute("sID", "1")),
                //    new XElement("sDoc", new XAttribute("sID", "1")
                    //)
                //)
        );
        Debug.WriteLine(xDoc);
        XElement xElementDocs = new XElement("Documents"); 
        XElement xElementsDoc;
        XElement xElementAdd;
        using (SqlConnection connection = new SqlConnection(connString))
        {
            connection.Open();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "select top 100 sID, sParID, docID, attBeg " + Environment.NewLine +
                ",[addDate],[mimeType],[caseID],[textSize],[textHash],[nativeFileName],[nativeFileSize]" + Environment.NewLine +
                ",[nativeMD5],[nativeUNC],[nativeDateCreate],[nativeDateModify],[nativeExtension]" + Environment.NewLine +
                "from docSVsys with (nolock)";
            SqlDataReader rdr = command.ExecuteReader();
            while (rdr.Read())
            {
                //Debug.WriteLine(rdr.GetInt32(0).ToString());
                xElementsDoc = new XElement("sDoc", new XAttribute("sParID", rdr.GetInt32(1).ToString()), new XAttribute("sID", rdr.GetInt32(0).ToString()));
                xElementsDoc.Add(new XElement("docID", rdr.GetString(2)));
                xElementsDoc.Add(new XElement("attBeg", rdr.GetString(3)));
                xElementDocs.Add(xElementsDoc);
            }
            rdr.Close();
        }
        xElementsDoc = (from xml2 in xElementDocs.Descendants("sDoc")
                        where xml2.Attribute("sID").Value == "2"
                        select xml2).FirstOrDefault();
        Debug.WriteLine(xElementsDoc);
        xElementsDoc.Add(new XElement("SVtext", "value"));
        xElementAdd = new XElement("MVtext1", "value1;value2");
        //xElement.Add(new XElement("value", "value1"));
        //xElement.Add(new XElement("value", "value2"));
        xElementsDoc.Add(xElementAdd);
        xElementsDoc = (from xml2 in xElementDocs.Descendants("sDoc")
                        where xml2.Attribute("sID").Value == "4"
                        select xml2).FirstOrDefault();
        Debug.WriteLine(xElementsDoc);
        xElementsDoc.Add(new XElement("SVtext", "value4"));
        xElementAdd = new XElement("MVtext1", "value41;value42");
        //xElement.Add(new XElement("value", "value1"));
        //xElement.Add(new XElement("value", "value2"));
        xElementsDoc.Add(xElementAdd);
        xDoc.Add(xElementDocs);
        //Debug.WriteLine(xDoc);
        xDoc.Save(@"C:\temp\xDocGabe.xml");

Will also build and XMLwriter version and compare performance. If anyone expresses and interest I will share my findings.

What I am finding is this problem is bigger than a bread box. There are 5 fk tables for multivalue fields that I need to get data from. The idea was to perform 6 queries then read through them using rdr.nextresult set. From a SQL perspective an efficient approach. What this approach requires is find the element using where xml2.Attribute("sID").Value == "X". At 1000 records it is done in seconds. At 10,000 is take minutes and I need it to scale to 100,000. The other problem I have is the I need the multivalue to appear as a single value with the value concatenated and separated with a ;. So I am either going to need to write SQL that flattens those columns or write and XML transform and I don't know how to do either. Or I may read those fk table results into a DictionaryList and use XMLwriter (sounds grunge but DictionaryList is fast). For now I am putting the feature out with 1000 max and Xdocument based. LINQ is convenient but it is not always fast.

Upvotes: 2

Views: 658

Answers (3)

John Saunders
John Saunders

Reputation: 161773

I believe you may be looking for the ROOT clause:

SELECT whatever
FROM wherever
FOR XML AUTO, ROOT('rootElementName')

Upvotes: 1

brainboost
brainboost

Reputation: 379

If your answer was performance in the easy-to-use/performance tradeoff then you should definitely use FOR XML clause as the sql-xml features built over performant msxml library and good optimized for forward reading.

Upvotes: 0

Mark W
Mark W

Reputation: 3909

Use LINQ to XML for anything related to XML in .NET.

http://msdn.microsoft.com/en-us/library/bb387089.aspx

Upvotes: 0

Related Questions