generate schemas for sql 2008 xml bulk load

Question

hey guys i have to import large sized xml data files (some of them 500 mb in size) into sql 2008. i plan to use sqlxml bulk load for the same.

however i am only interested in certain elements of the xml data file, not each and every element. my question is: in the schema file do i need to mention each and every element present in the xml data file or only those elements which i am interested in loading

second question is: is there any tool by which i can automate the generation of these schema files for sqlxml bulk load as i have very little knowledge of xsd / xdr

thanks in advance

Answer 1

I ran into this old question looking for something else, but I will answer it anyway, for future reference.

1. To ignore certain elements, you can use:

   • sql:mapped="false" to ignore a node totally, including its children (see sql:mapped (SQLXML 4.0))
   • sql:is-constant="true" to ignore a container-node, but continue processing the children (see Creating Constant Elements Using sql:is-constant (SQLXML 4.0))
   • sql:overflow-field="OverflowColumnName" to write everything you did not map to an extra column (see sql:overflow-field (SQLXML 4.0))

2. Tot automate the generation of schema files, you can use xsd.exe to generate the schema, but then you will have to add relationships yourself, see the XML Bulk Load Examples on MSDN. I don't know anything about other tools, but I seem to remember some references to tools generating the relationships.
   Edit I just ran into this old post How can I create database tables from XSD files?. Of course generating schemas from .xsd is one step further than .xml, but it will save some time, and maybe these same tools can also generate it from XML? (Besides these tools were mentioned in 2008, but hey, it never hurts to try.) It mentions these tools:

   • Altova's XML Spy
   • XSD2DB on SourceForge
   • Axis wsdl2java
   • hyperjaxb
   • hand written XSLT

Answer 2

You can use XSD.exe to create the schema from the XML data file.