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 Online XML file into SQL Server 2005 tables – no data transferred

Does anyone have any great suggestions on how I can import an online XML file into an SQL 2005 table?

So far I tried to accomplish it with SSIS Data Flow Task where my source is XML Source (Data access mode: XML file location; XML location: URL, Use inline schema = True). This set up properly identified the columns to be imported.

I used Copy Column data flow transformation task to load data to OLE DB destination table that has same structure.

When I run the task it does execute with no errors, however the table remains empty.It looks like I am failing to read the actual data.

Do you have any suggestions?I am willing to go around this approach with stored procs/com/you name it – just make it work!

Thanks a lot for your help!

LoveDanger

First of all, does any data enter the pipeline from the XM source? Use a data-flow viewer to find out.

-Jamie

|||Nope. No rows affected during the execution.. When I work with the data flow viewer, and point to the URL in the XML Source, it does read the columns of the XML output. Doesn't read the data though..|||

Jamie,

Thanks for the reply. Here is more: I copied xml and tried to import it into my table using sp_xml_prepareDocument ...OPENXML.

I still got the same result: 0 rows affected. Any suggestions? The table structure is identical to the XML output. The OpenXML qry uses the following syntax:

FROM OPENXML(@.xmlHndAdd, '/NewDataSet/Table1', 1)
WITH MyTbl

The XML file format is:

<NewDataSet xmlns="">
<Table1 diffgr:id="Table11" msdata:rowOrder="0">
<program_id>1-2-3-4-5</program_id>
<object_name />
</Table1>
<Table1 diffgr:id="Table12" msdata:rowOrder="1">
<object_id>6-7-8-9-0</object_id>
<object_name>ABC</object_name>
<objectproperty_id>1-3-5-7-9</objectproperty_id>
</Table1>

Thank you!

|||I have the same setup that works without "diffgr:id="TableN" msdata:rowOrder..." if I simply set <table1> - the rows can be inserted just fine... Any more suggestions?|||

Oh.. it was a matter of syntax. Here is the answer:

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE MyTbl

INSERT MyTbl SELECT * FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)

WITH MyTbl

Importing Exchange Contacts to SQL

I have been trying to accomplish the same thing but have not been able to do
so in SQL. Were you able to accomplish this task?
"Terry" wrote:

> Hi everyone,
> We have a contacts database on SQL. but also a large number of users stori
ng
> contacts within Exchange.
> What I'd like to do is be able to extract Exchange Contact data, maybe on
a
> nightly basis, into SQL.
> What would be the best way to achieve this? Could i use a linked server?
> I've found various snippets of VBA, but nothing that looks like it could
> achieve what i need.
> Any ideas?
>
>http://msdn.microsoft.com/library/d...ado_objects.asp
This lists how to connect to exchange using ADO.
I did this once (and I don't claim this is the right way) to see if it was
possible.
MS access comes with a driver to link Exchange/outlook folders.
You can link the global address list under "address books".
Then import the contacts to SQL Server using DTS.
There are a few different ways I can think of doing this, the above simply
being the easiest (not the right way).
Simon Worth
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:19F1C665-AAAF-4F72-9ADB-6FAC7B3F85F5@.microsoft.com...
> I have been trying to accomplish the same thing but have not been able to
do
> so in SQL. Were you able to accomplish this task?
> "Terry" wrote:
>
storing
on a|||Simon,
Thank you. I'm not major programmer, I have some things but mostly in SQL
and some Active X scripting. Do I have to Visual Studio to use this code in
the link that you provided or can I use this in DTS ActiveX?
Thanks again for your response, I hope I can use it.
"Simon Worth" wrote:

> http://msdn.microsoft.com/library/d...ado_objects.asp
> This lists how to connect to exchange using ADO.
> I did this once (and I don't claim this is the right way) to see if it was
> possible.
> MS access comes with a driver to link Exchange/outlook folders.
> You can link the global address list under "address books".
> Then import the contacts to SQL Server using DTS.
> There are a few different ways I can think of doing this, the above simply
> being the easiest (not the right way).
> --
> Simon Worth
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:19F1C665-AAAF-4F72-9ADB-6FAC7B3F85F5@.microsoft.com...
> do
> storing
> on a
>
>|||Oh another thought,
I want to be able to do this from the Server level, meaning being able to
pull the contacts from every user that is a member of a particular group.
Therefore, I'm hoping that this is something that I would not have log on to
every user's mailbox individually to export their contact folder.
"Simon Worth" wrote:

> http://msdn.microsoft.com/library/d...ado_objects.asp
> This lists how to connect to exchange using ADO.
> I did this once (and I don't claim this is the right way) to see if it was
> possible.
> MS access comes with a driver to link Exchange/outlook folders.
> You can link the global address list under "address books".
> Then import the contacts to SQL Server using DTS.
> There are a few different ways I can think of doing this, the above simply
> being the easiest (not the right way).
> --
> Simon Worth
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:19F1C665-AAAF-4F72-9ADB-6FAC7B3F85F5@.microsoft.com...
> do
> storing
> on a
>
>