Get great amount of data with T-SQL for xml
8 May 2008I’m creating some big xml files to test performance of linq to xml to make some PoC. I use simply the ForXml to extract data from Customer and Orders table of northwind database, The first Xml is 517 Kb, but I need really bigger file.
The trick is simple I created another table called insertHelper that contain a single column, and I filled with numbers from 1 to 100:
Now I simply issue this query to create a very big xml file
select C.[CustomerID] + convert(varchar(2), H.[value]) [CustomerID] ,C.[CompanyName] ,C.[ContactName] ,C.[ContactTitle] ,C.[Address] ,C.[City] ,C.[Region] ,C.[PostalCode] ,C.[Country] ,C.[Phone] ,C.[Fax] ,orders.* from customers C cross join inserthelper H inner join orders on orders.customerID = C.customerID for xml AUTO, ELEMENTS, ROOT('Customers')
This makes me happy because now I have the original customer x 100 record, I have ALFKI1, ALFKI2..ALFKI100 and so on, thanks to the cross join SQL operator. The problem is that the XMl returned is so big that when I try to open in the Sql Server management Studio I received an “Insufficent memory” because the XML is too big. The solution is to use this fragment of code.
SqlCommand cmd = conn.CreateCommand(); conn.Open(); cmd.CommandText = @"select C.[CustomerID] + convert(varchar(2), H.[value]) [CustomerID] ,C.[CompanyName] ,C.[ContactName] ,C.[ContactTitle] ,C.[Address] ,C.[City] ,C.[Region] ,C.[PostalCode] ,C.[Country] ,C.[Phone] ,C.[Fax] ,orders.* from customers C cross join inserthelper H inner join orders on orders.customerID = C.customerID for xml AUTO, ELEMENTS, ROOT('Customers')"; XmlReader res = cmd.ExecuteXmlReader(); FileStream fs = new FileStream("Big.xml", FileMode.OpenOrCreate); XmlDocument doc = new XmlDocument(); doc.Load(res); XmlWriter writer = XmlWriter.Create(fs); doc.WriteTo(writer);
This is not good snippet, it lack using for SqlCommand (that is disposable) but it serves me only to create the file thanks to the ExecuteXmlReader functino of the SqlCommand.
The file is 60Mb big, and is a good candidate to make some load test of linq to XML.
Alk.
Tags: ForXml Linq to XML
One Response to “Get great amount of data with T-SQL for xml”
July 3rd, 2008 at 4:04 am
[...] I told in a previous post if you have to retrieve a great amount of XML generated by a FOR XML in a sql server 2005 [...]