SQL Server 2008 / XML Bulk Load Mapping Schema / Lookup Foreign Key ID

Question

I am trying to create an XML mapping schema for SQL Server 2008 XML bulk upload. My XML data file contains data that will populate multiple tables in my database. So far, I am able to create a mapping schema that will populate multiple tables. The problem that I have is that I have a lot of "lookup" columns in my database tables. These "lookup" tables contain two columns: (1) a primary key ID (INT) column and (2) a name (VARCHAR) column with a unique constraint. The tables that contain a foreign key reference to these lookup tables reference the primary key ID column and not the name. The problem is that my XML data files contains the name of the lookup and not the primary key ID. Is there a way in the mapping schema to automatically convert the name of these lookups to their corresponding primary key IDs?

Answer 1

I have not yet found an answer to my question regarding the SQL Server 2008 XML bulk upload schema. However, it occured to me that another way of doing this is to use an XSLT (Extensible Stylesheet Language Transformation) that will take the XML file and the transform the data into SQL insert statements. With this method it should be fairly "easy" to generate the SQL select statments that will convert the "lookup" names into their corresponding IDs.