How to split +2Gb XML into 500Mb chunks?

PHEC · April 8th, 2012, 07:57 PM

I have several +2Gb XML files (from openstreetmap.org as .OSM) which I want to 'shred' to SQL server. For that 2Gb-1 byte is the max size I can handle.

The structure is like this:

Code:

<?xml version='1.0' encoding='UTF-8'?>
<osm version="" generator="">
  <node id="" lat="" lon="" version="" changeset="" user="" uid="" timestamp=""/>
  <node id="" lat="" lon="" version="" changeset="" user="" uid="" timestamp="">
      <tag k="" v="" />
      <tag k="" v="" />
  </node>
  <way id="" version="" changeset="" user="" uid="" timestamp="">
      <nd ref=""/>
      <nd ref=""/>
      <tag k="" v="" />
      <tag k="" v="" />
  </way>
  <relation id="" version="" changeset="" user="" uid="" timestamp="">
      <member type="" ref="" role=""/>
      <member type="" ref="" role=""/>
      <tag k="" v="" />
      <tag k="" v="" />
  </relation>
</osm>

In short, millions of nodes , followed by millions of ways, followed by millions of relations, so splitting into one file per node/way/relation is not really an option.

I don't know that much about XML and don't even know if current XSLT processors can handle +2Gb sizes.

I managed to read the file line by line into a table and use a cursor in T-SQL to combine the lines to well-formed XML chunks for further processing. It does the trick, but takes 'forever' (several hours) for even far smaller files (60Mb). So I was wondering if XSLT 2.0 transform could speed up the chunking.

Cheers

mhkay · April 9th, 2012, 03:38 AM

Most XSLT processors can't handle anything approaching this size. You'll need a processor that handles streaming, such as Saxon-EE. However, this is still bleeding edge, so I'm a bit reluctant to recommend it to a newbie: there's certainly a learning curve. You say you want the data in 500Mb chunks, but I wonder if that's really your requirement? The question is, what exactly will SQL Server accept that ends up creating a useful database?

Rather than splitting the data, another approach might be to write a program that reads the XML in a streaming mode, and issues SQL insert instructions for each "record" that it encounters.

Another approach might be to try and persuade OSM to generate multiple smaller chunks of XML by refining your search criteria.

PHEC · April 10th, 2012, 02:29 PM

Thanks for your thoughts.

Yes there is SSIS, which is probably capable to loading this straight into tables.

And No, I cannot be absolutely sure that the smaller subsets (counties) of my area of interest (UK) actually cover the whole UK until I've tested this (there are a few counties missing, but they could be incorporated other ones.

As for requirements, this is just trying to replicate openstreetmap.org db functionality in SQL Server.

I don't mind a bit of a learning curve, though I have to admit that XML is not on my plate regularly, so I usually end up re-inventing even my own wheels in the rare occasion where I need do do something with XML.

Cheers