Exporting to XML and importing from XML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Exporting to XML and importing from XML

Author

Topic

shaunos
Starting Member

8 Posts

Posted - 2011-10-14 : 04:09:20

Hi,

Here at work we currently send volume orders every day to our suppliers via CSV files and receive order updates from them, also in CSV format.

However we are currently looking at doing business with a new supplier who require that all data is transferred via XML.

I've been google-ing around trying to find the best way to achieve the export/import of data in XML but I can't seem to find a clean solution. bcp export looks a little hacky, but I could be wrong...

Below is an example of the type of XML we need to handle.
The file would have a header and footer and then any number of the following:
--------------------------------------------
<ORDER>
<HEADER>
<COMPANY_CODE>EXT</COMPANY_CODE>
<TRANSACTION_ID>2023030</TRANSACTION_ID>
<DATE_TIME_STAMP>07/10/2011 16:43:04</DATE_TIME_STAMP>
</HEADER>
<ORDER_DATA>
<COMPANY_DETAILS>
<NAME>ABCDEFG</NAME>
<CODE>99999</CODE>
<COMPANY_ACCOUNT_NUMBER>99999999</COMPANY_ACCOUNT_NUMBER>
</COMPANY_DETAILS>
<ORDER_DETAILS>
<ORDER_NUMBER>XX_111007_1648955</ORDER_NUMBER>
<TYPE>PROVIDE</TYPE>
<APPLICATION_DATE>07/10/2011</APPLICATION_DATE>
<ACCOUNT_OPTION>NA</ACCOUNT_OPTION>
<PRODUCT_DETAILS>
<PRODUCT_CODE>PROD123</PRODUCT_CODE>
<ACTION>A</ACTION>
</PRODUCT_DETAILS>
<PRODUCT_DETAILS>
<PRODUCT_CODE>PROD456</PRODUCT_CODE>
<ACTION>A</ACTION>
</PRODUCT_DETAILS>
</ORDER_DETAILS>
<CUSTOMER_DETAILS>
<ACCOUNT_NUMBER>999999999</ACCOUNT_NUMBER>
<NAME>
<OCCUPANT_NAME>Power</OCCUPANT_NAME>
</NAME>
</CUSTOMER_DETAILS>
<EXISTING_CUSTOMER>
<TELE_NO>099-9999999</TELE_NO>
<AS_OPTION>A</AS_OPTION>
<REPAIR_SLA>PRIORITY</REPAIR_SLA>
<SERVICE_DETAILS>
<SERVICE_CODE>SLA1</SERVICE_CODE>
</SERVICE_DETAILS>
</EXISTING_CUSTOMER>
</ORDER_DATA>
</ORDER>
-----------------------------------------------------

Any suggestions as to how I can best generate and read in this XML?

Even better, anyone got some good 'hello world' examples anywhere that I could look at?

I should add that this needs to be 100% automated, i.e. a job runs a couple of times a day sending new orders and another job runs periodically importing updates....

FWIW we're running SQLSERVER 2000 on a win2K box (migrating to SQLSERVER 2008 in next few months)

Cheers,

~Shaun.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 04:34:50

you need to store xml as it is? if yes look for OPENROWSET BULK in google
If you need to shred and store data then use OPENXML

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shaunos
Starting Member

8 Posts

Posted - 2011-10-14 : 05:08:20

Thanks for the reply.

At a very basic level our processes need to look like
ORDER:
select * from view_ORDERS ---> Some XML transformation ---> ORDER.xml
ftp ORDER.xml to supplier

ORDER UPDATES:
ftp get order updates from supplier (UPDATE.xml)

UPDATE.xml ----> Some means to import XML contents to a temp_processsing_table.
Step through temp_processing_table and update customer information in database accordingly.

All the examples of OPENXML that I've seen seem to work by slurping the entire XML file into a varchar and processing it. But a varchar is limited to 8000 characters which isn't very much considering the amount of characters in some XML files. Am I missing something here?

Cheers,

~Shaun.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 05:14:19

you can make it text type if you want to get over 8000 limit.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources