Generate XML From SQL

Question

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.

@JohnSaunders I went from knowing nothing about writing XML 2 days ago to working code. I have a rating over 1000 and have only asked 10 questions is that really the profile of someone that gives no effort? — paparazzo
– paparazzo, Commented Dec 11, 2011 at 14:19
I said, "no apparent effort". You now appear to have made an effort, so I'll upvote. — John Saunders
– John Saunders, Commented Dec 11, 2011 at 17:34
And I still don't know how to get from TSQL FOR XML AUTO to a well formed XML document. Just adding text that happens to be XML would work but I don't trust my ability to create a well formed XML from text. — paparazzo
– paparazzo, Commented Dec 11, 2011 at 17:45

John Saunders · Accepted Answer · 2011-12-11 18:12:02Z

1

I believe you may be looking for the ROOT clause:

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

answered Dec 11, 2011 at 18:12

John Saunders

162k26 gold badges252 silver badges403 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

paparazzo Over a year ago

I still had to add the top line

paparazzo Over a year ago

I still had to add the top line: <?xml version="1.0" encoding="UTF-8" ?> Closest answer I got so I give the check. But I guess I am comfortable with that. I know this is basically a stupid question but XML kind of scares me. At this point I don't use XMLwriter as I have XML? Just use StreamWriter to write directly to file and write that ?xml line then use SQLdataReader to read the TSQL output and write it to file a line at at time? I just can't think of how to make it any more efficient. But to be honest XDocument is pretty darn fast - I could live with that.

Mark W · Accepted Answer · 2011-12-10 15:22:30Z

0

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

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

answered Dec 10, 2011 at 15:22

Mark W

3,9092 gold badges40 silver badges53 bronze badges

1 Comment

paparazzo Over a year ago

OK, thanks, but my question was a bit more specific. Should I query using the FOR XML AUTO for SQL to generate some of the XML or query regular and build the XML from the raw data?

brainboost · Accepted Answer · 2011-12-11 00:00:59Z

0

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.

answered Dec 11, 2011 at 0:00

brainboost

3792 silver badges14 bronze badges

1 Comment

paparazzo Over a year ago

Thanks, then how do you recommend I add the top <?xml version line and and the root opening and closing tags. I don't know what you mean by "performant msxml" - do I need to use it directly or that is why FOR XML is fast? The FOR XML is definitely fast - but I am having trouble taking that to a well formed XML document that will load into Excel.

Collectives™ on Stack Overflow

Generate XML From SQL

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related