How to load a huge (but simple) hierarchical XML file into an SQL table

Question

I have a very large (2.5 GB, 55 million node) XML file with the following format:

<TopNode> <Item id = "Something"> <Link>A link</Link> <Link>Another link</Link> <Link>One More Link</Link> </Item> <Item id = "Something else"> <Link>Some link</Link> <Link>You get the idea</Link> </Item> </TopNode> 

I want to flatten this in the following SQL table:

  ----------------------------------------- | Item | Link | ----------------------------------------- | Something | A link | | Something | Another link | | Something | One More Link | | Something Else | Some Link | | Something Else | You get the idea | |----------------|----------------------| 

I am using SQL2008 if that matters.

What is the easiest and most efficient way (preferably using the SQL Server / .NET stack) to get from point A to point B, bearing in mind the file size?

Answer 1

I would use XML Bulk Load . This is a good approach because it is not readable in the entire document at once; it conveys it. It is also pretty fast and supports your requirement to stick with the SQL Server tool.

Answer 2

Take a look at Oslo / M.