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]
