Get great amount of data with T-SQL for xml

2008, May 08 2 mins read

I’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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
           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