Importing Table Data Into an XML Column

I've been reading through BOL and various forms and am even more confused now as to what the best option is for what I need to accomplish...here's the scenario.

We have a staging table comprised of the following columns:

CN_CUST_KEY

ITM_SUF_NO

Model

Serial

SaleYear

SaleDate

prd_itm_no

LastName

MiddleName

FirstName

StreetAddress

...etc.

We need to load the data into a table with a similar structure, except that the first seven columns will be inserted into a column called CustomColumns, which has an XML datatype. The other columns will be inserted into their correlating columns.

I've already created the XSD and bound it to the XML column in my table:

Code Snippet

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:element name="CustomColumns">

<xsd:complexType>

<xsd:complexContent>

<xsd:restriction base="xsd:anyType">

<xsd:sequence>

<xsd:element name="CN_CUST_KEY">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

<xsd:element name="ITM_SUF_NO">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

<xsd:element name="Model">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

<xsd:element name="Serial">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

<xsd:element name="SaleYear">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

<xsd:element name="SaleDate">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

<xsd:element name="prd_itm_no">

<xsd:simpleType>

<xsd:restriction base="xsd:string" />

</< FONT>xsd:simpleType>

</< FONT>xsd:element>

</< FONT>xsd:sequence>

</< FONT>xsd:restriction>

</< FONT>xsd:complexContent>

</< FONT>xsd:complexType>

</< FONT>xsd:element>

</< FONT>xsd:schema>

So at the end of the day, the contents of any onw row's CustomColumns content would resemble:

Code Snippet

<CustomColumns>

<Lender></Lender>

<Dealership></Dealership>

<PayoffDate></PayoffDate>

<ConsentSigFlag></ConsentSigFlag>

<ConsentDateFlag></ConsentDateFlag>

<ConsentLenderFlag></ConsentLenderFlag>

<ContactCRAFalg></ContactCRAFalg>

<OwnerSigFlag></OwnerSigFlag>

<OwnerCompanyFlag></OwnerCompanyFlag>

<CertificateNo>QA095407</CertificateNo>

<AgentName>COLONIAL HARLEY DAVIDSON</AgentName>

<InsEFfDate>07/08/2006</InsEFfDate>

<HouseHoldClmQty>0</HouseHoldClmQty>

<HouseHoldClmSeq>0</HouseHoldClmSeq>

</CustomColumns>

...except of course with the appropriate column names.

One of the requirements of the process is that we're able to pop it into an SSIS pacakge for repeatable execution and modification whenever there is a new set of data points.

If anyone can provide any guidance, it would be greatly appreciated.

Thanks!!

I think I'm close....

Here's the query I ran:

Code Snippet

INSERT INTO [clmnt]

(

name1,

addr1,

addr2,

city,

[state],

zip5,

zip4,

CustomColumns)

SELECT

LastName,

StreetAddress1,

StreetAddress2,

City,

[State],

Postal5,

Postal4,

(SELECT CN_CUST_KEY,

ITM_SUF_NO,

Model,

Serial,

SaleYear,

SaleDate,

prd_itm_no

FROM TblClass_Customers FOR XML PATH, ELEMENTS, ROOT('CustomColumns'), TYPE)

FROM TblClass_Customers;

...and here are the results:

Msg 6965, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s):CN_CUST_KEY where element 'row' was specified. Location: /*:CustomColumns[1]/*:row[1]