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]

Importing data from various data sources with non standard formats

Hi all :)

I'm wondering if SSIS will be the solution to the problem I'm working on.

Some of our customers give us an Excel sheet with data they want to insert or update in the database.

I've created a package that will take an Excel sheet, do some data conversion so the data types match up and after that I use a Slowly Changing Data component to create the insert/update commands.

This works great. If a customer adds a new row to the Excel sheet or updates an existing row changes are nicely reflected in the database.

But now I’ve got the following problem. The column names and the order of the columns in the Excel sheet are not standard and in the future it could happen a customer doesn't even use an Excel sheet but something totally different.

Can I use SSIS for this? Is it possible to let the user set the mappings trough some sort of user interface? I’ve looked at programmatically creating the package but I’ve got to say that’s quit hard to do… It would be easier to write the whole thing myself than to create the package trough code ;)

If not I thought about transforming the data in code before I pass it on to the SSIS package in something like XML. That way I can use standard column names and data types.

So how should I solve this problem? Use SSIS or not?

Thnx :)

Wouter de Kort

A question and comment:

1. Why are you using SCD for insert/updates on non-dimension data? It doesn't sound like you have a warehouse and a simple insert/update scenario should work (lookup data, if row not found, then insert, else update). But as long as it's working for you, that's ok.

2. You will have various problems with non-standard columns or file formats. You need to get your customer to send a defined format. One way around this is to create a staging package, loading the data into staging tables and then running the insert/updates on it from a standardized interface. There are numerous ways to set up staging, and I use VIEWS for mine as they are easy to replicate.

Wes|||

1. I already thought that SCD was a little bit overkill for this problem but it has the logic build in to check if a row is already present -> update it and otherwise it will delete it and that was just the thing I need. Would it be easier to 'duplicate' this functionality by using a lookup?

2. Off course it would be easiest to let the customer send a defined format... but we're not in the position the demand that from them (maybe in the future :))

I've started building code to convert the format the user sent to a standard xml format which I can feed to my package and the user can supply the mapping between the data and the XML schema. So they can send in whatever they want... the only ′problem′ is that I will have to build code that manually reads an Excel/Text/.... file and parse that to create the XML structure so I replicate some things that are already in SSIS :(

Could you tell me somewhat more about your staging package? Is that just an Insert into a custom build table that will hold the data? And then use a View to map it to the structure the SSIS package wants?

Wouter :)

|||

The lookup method I'm talking about does a similar function as the SCD. It just sounds like you are doing constant updates which violates the idea of "slowly" changing dimensions. I'm not too keen on the SCD stage in SSIS so I prefer to build my own lookup/update mechanism unless it truly is a type 2 SCD, but like I said whatever is working for you that meets the requirements is sufficient. (look at the sticky at the top of the forums referencing Jamie Thompson's blog and how he handles this)

Before I explain my staging, how often do the clients change their file formats? I don't want to lead you on the wrong track.

Thanks.

|||

Some of our clients send in an Excel sheet once a week with approximately 600 records. The strange thing is there system doesn't tell them if some stores are new or some are updated... it just gives the whole list.

But as I already said this could be changing in the future. Some other clients have requested Import functionality so they can connect their system/data to ours.

But what do you understand as slowly changing? My data has some modifications, some new records on each update so I thought this would be handy because it nicely encapsulates the logic?

Wouter :)

|||

Razorblade wrote:

Some

of our clients send in an Excel sheet once a week with approximately

600 records. The strange thing is there system doesn't tell them if

some stores are new or some are updated... it just gives the whole list.

But as I

already said this could be changing in the future. Some other clients

have requested Import functionality so they can connect their

system/data to ours.

But what

do you understand as slowly changing? My data has some modifications,

some new records on each update so I thought this would be handy

because it nicely encapsulates the logic?

Wouter :)

Your input list is fine if it's mixed data. As stated, you can

use the lookup function to process the list and sort into new/inserted

rows, and update or insert accordingly.

Nothing you have sounds slowly changing dimension(al), which is why I

questioned your use of the SCD wizard. SCD (in theory) is

for dimensions that don't change much, say a customers' home address or

an employees' position. But if the SCD wizard is working for you,

use it.

Go ahead and mark your question as answered if you are satisfied.

Thanks.

Importing a SQL Database using SQLDMO.. URGENT HELP REQUIRED..

I have written an application in Microsoft Access. I'm currently using SQL
Server 2000 as my datatabase, and use SQLDMO to perform various functions
like Backup, Restore and copy. Now I want to create a new database from a
template database which I have backed up to a template.mdf file. Now I want
to create a new database, and then import the template file into the New
database.
Basically this is the steps that I need to follow.
1) Create Blank SQL Server Database - Can already do this.
2) Import Template database into new blank sql server database. - This is
where the problem occurs.
If there is an easier way of doing this then It will also be appreciated..
bascially If I can just specify the template file and say to the application
create me a new database from that file then it will also be fine.
Any help will be greatly appreciated!!if you'll be using the template many times
might as well do it in the model database
any thing you create will be patterned with the model databse
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Db_Stuff" wrote:

> I have written an application in Microsoft Access. I'm currently using SQL
> Server 2000 as my datatabase, and use SQLDMO to perform various functions
> like Backup, Restore and copy. Now I want to create a new database from a
> template database which I have backed up to a template.mdf file. Now I wan
t
> to create a new database, and then import the template file into the New
> database.
> Basically this is the steps that I need to follow.
> 1) Create Blank SQL Server Database - Can already do this.
> 2) Import Template database into new blank sql server database. - This is
> where the problem occurs.
>
> If there is an easier way of doing this then It will also be appreciated..
> bascially If I can just specify the template file and say to the applicati
on
> create me a new database from that file then it will also be fine.
> Any help will be greatly appreciated!!
>|||I am still a beginner at this and I have only managed to get so far with the
help of various sources on the internet. I will be using the template
basically all the time to create a new database, but what do you mean I migh
t
as well do it in the Model Database..
thanks.
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> if you'll be using the template many times
> might as well do it in the model database
> any thing you create will be patterned with the model databse
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Db_Stuff" wrote:
>|||the model database is the template database of sql server
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Db_Stuff" wrote:
> I am still a beginner at this and I have only managed to get so far with t
he
> help of various sources on the internet. I will be using the template
> basically all the time to create a new database, but what do you mean I mi
ght
> as well do it in the Model Database..
> thanks.
>
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>|||hi,
Db_Stuff wrote:
> I have written an application in Microsoft Access. I'm currently
> using SQL Server 2000 as my datatabase, and use SQLDMO to perform
> various functions like Backup, Restore and copy. Now I want to create
> a new database from a template database which I have backed up to a
> template.mdf file. Now I want to create a new database, and then
> import the template file into the New database.
Jose already introduced you the way new databases are created, actually a
"clone" of model system database...
if you want your application databases all inherit from your particular
template, why not just provide a backup of it and perform a restore
operation specifying a new database name and providing the WITH MOVE terms
to generate the relative physical files in the correct (and obviously
different) position?
BTW, I usually prefer all databases inherit from target model database and
create all required dbobjects via standard Transact-SQL scripts...
the best deployment article I ever read for programmers can be found at
http://msdn.microsoft.com/msdnmag/i...abaseinstaller/
..this kind of soulution takes care of appropriate versioning and successiv
e
database schema updates...
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply