Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    167

    Unhappy Unanswered: importing from a grouped xml file

    All the examples I've found on the web for importing xml to a table use a simple, flat format for the xml:

    Code:
    <record><recordID>1</recordID><name>John</name></record>
    <record><recordID>2</recordID><name>Robert</name></record>
    But I'm trying to import a file with hierarchical groups:

    Code:
    <groups>
    <group GroupID="115" Group Description="First Group">
    <item><itemID>1</itemID><Name>Item One</Name><ItemAttributes><CatalogNumber></CatalogNumber><Size></Size><Color></Color></ItemAttributes></item>
    <item><itemID>2</itemID><Name>Item Two</Name><ItemAttributes><CatalogNumber></CatalogNumber><Size></Size><Color></Color></ItemAttributes></item>
    </group>
    <group GroupID="2576" Group Description="Second Group">
    <item><itemID>3</itemID><Name>Item Three</Name><ItemAttributes><CatalogNumber></CatalogNumber><Size></Size><Color></Color></ItemAttributes></item>
    <item><itemID>4</itemID><Name>Item Four</Name><ItemAttributes><CatalogNumber>ItemFourL</CatalogNumber><Size>L</Size><Color></Color></ItemAttributes><ItemAttributes><CatalogNumber>ItemFourM</CatalogNumber><Size>M</Size><Color></Color></ItemAttributes></item>
    </group>
    </groups>
    I'm wanting to import this to a flat staging table: STAGE(GroupID int, GroupDescription varchar(300), ItemID int,ItemName varchar(300), CatalogNumber varchar(300),Size char(1),Color varchar(3)).

    Can anyone give pointers or point me to an instruction source for importing this type of xml to a relational table? I've been playing around with openxml and the .nodes() function, but I've had no luck with the groupings and nested attributes shown in the last item.

  2. #2
    Join Date
    Nov 2003
    Posts
    167
    In case anyone comes across this in a search for grouped/multi-level/complex xml, I've built a solution using SSIS and the xml source object to import into three different staging tables (one for each "level" or "grouping" of xml data). I then create a flat table using sql and the three staging tables.

    One problem I encountered was the SSIS/.net limitation of 4000 characters max for string output. One of my field's attributes hovers around 7000 characters and needs to go into a varchar(7200) column in the destination table. To get around this, I changed the xml source's external column to unicode text stream [DT_NTEXT] and loaded into an nvarchar(max) column in the staging table.

    While this works, it's a bit awkward since this data needs to go into a sql server 2000 database and sql2k doesn't support nvarchar(max). I had to deploy the package and staging tables to a sql08 server and push the data to the sql2k server via a linked server. For this reason - I'm still looking for a scriptable solution using .nodes() and a couple of cross applies.

    An outline of this solution can be seen as solution # 2 at this link (starts at 10:40 mark and continues into the next video):
    Process Multi-Level XML in SSIS - Part 2 - YouTube

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •