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.
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.