2

I have a set of xml files that I want to parse the data of and import in to a sql server 2012 database. The provided xml files will be validated against a schema.

I am looking as to what is the best method of doing this is. I have found this: http://msdn.microsoft.com/en-us/library/ms171878.aspx

I am wondering if this is the best way or if there are others?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
amateur
  • 43,371
  • 65
  • 192
  • 320

1 Answers1

0

You have several options:

  1. SSIS XML Source. This does not validate against the schema. If you want to detect and properly handle invalid XML files, create a script task to validate the schema in C#.

  2. Parse the XML in a stored procedure.

    • Insert the entire XML file in one column. Depending on your schema validation requirements, you can use an untyped or typed XML column. (Or both)
    • Parse the XML using XPath functions. This is actually very fast.
    INSERT INTO SomeTable (Column1, Column2, Column3)
    SELECT
        YourXmlColumn.value('(/root/col1)[1]','int'),
        YourXmlColumn.value('(/root/col2)[1]','nvarchar(10)'),
        YourXmlColumn.value('(/root/col3)[1]','nvarchar(2000)'),
        YourXmlColumn.value('(/root/col4)[1]','datetime2(0)')
    FROM YourXmlTable
S Koppenol
  • 205
  • 1
  • 9