Complex XML load - SQL Server Forums

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)

Complex XML load

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-09-25 : 07:21:03

Sergio writes "Hi,

I need to load a large and complex XML file into SQL server 2000.

I want to use SQLXML 3.0 and XSD. The trouble is all the sample files I have looked at have a very simple structure.

My file looks a bit like this.
<?xml version="1.0"?>
<PLACEMENTS>
<FILE_NAME>somefile.xml</FILE_NAME>
<CREATED>01/01/2006 </CREATED>
<ACCOUNTS>
<ACCOUNT>
<TYPE>Some Type Or other</TYPE>
<ACCOUNT_NO>9999</ACCOUNT_NO>
<BALANCE>9.76</BALANCE>
<PRODUCTS>
<PRODUCT>
<TYPE>NORMAL</TYPE> <START_DATE>10/12/2003</START_DATE>
<END_DATE>23/01/2006</END_DATE>
<REFERENCE>d/4534535</REFERENCE>
<PAYMENT>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
</PAYMENT> </PRODUCT>
<PRODUCT>
<TYPE>NORMAL</TYPE> <START_DATE>10/12/2003</START_DATE>
<END_DATE>23/01/2006</END_DATE>
<REFERENCE>d/4534535</REFERENCE>
<PAYMENT>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
</PAYMENT>
</PRODUCT>
</PRODUCTS>
<HISTORY>
<BILL>
<BDATE>10/02/1999</DATE>
<BTYPE>STANDARD</TYPE>
<BILL_TOTAL>35.61</TOTAL_CHARGES>
<BALANCE>31.76</BALANCE>
</BILL>
<BILL>
<BDATE>10/02/1999</DATE>
<BTYPE>STANDARD</TYPE>
<BILL_TOTAL>35.61</TOTAL_CHARGES>
<BALANCE>31.76</BALANCE>
</BILL>
<BILL>
<BDATE>10/02/1999</DATE>
<BTYPE>STANDARD</TYPE>
<BILL_TOTAL>35.61</TOTAL_CHARGES>
<BALANCE>31.76</BALANCE>
</BILL>
</HISTORY>
</ACCOUNT>
</ACCOUNTS>

There will be 10,000 records like this per day. Products, payment and BILL have a <..maxOccurs="unbounded"/>.

I have an XSD but it uses <xsd:complexType... sql:relation="[Some Table]> and the SQLXML3.0 throws an error at this.

Is there anyway of doing this without having to write an application.

many thanks

"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-25 : 10:43:33

Here's an example of how to use the OpenXml function to create rowsets from the XML. I copied the account so that it shows a second account. Note that the XML you gave in your example is not well formed, there are mismatched closing tags for some of the elements.

DECLARE @idoc int
declare @doc varchar(8000)
set @doc =
'<?xml version="1.0"?>
<PLACEMENTS>
<FILE_NAME>somefile.xml</FILE_NAME>
<CREATED>01/01/2006 </CREATED>
<ACCOUNTS>
<ACCOUNT>
<TYPE>Some Type Or other</TYPE>
<ACCOUNT_NO>9999</ACCOUNT_NO>
<BALANCE>9.76</BALANCE>
<PRODUCTS>
<PRODUCT>
<TYPE>NORMAL</TYPE> <START_DATE>10/12/2003</START_DATE>
<END_DATE>23/01/2006</END_DATE>
<REFERENCE>d/4534535</REFERENCE>
<PAYMENT>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
</PAYMENT> </PRODUCT>
<PRODUCT>
<TYPE>NORMAL</TYPE> <START_DATE>10/12/2003</START_DATE>
<END_DATE>23/01/2006</END_DATE>
<REFERENCE>d/4534535</REFERENCE>
<PAYMENT>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
</PAYMENT>
</PRODUCT>
</PRODUCTS>
<HISTORY>
<BILL>
<BDATE>10/02/1999</BDATE>
<BTYPE>STANDARD</BTYPE>
<BILL_TOTAL>35.61</BILL_TOTAL>
<BALANCE>31.76</BALANCE>
</BILL>
<BILL>
<BDATE>10/02/1999</BDATE>
<BTYPE>STANDARD</BTYPE>
<BILL_TOTAL>35.61</BILL_TOTAL>
<BALANCE>31.76</BALANCE>
</BILL>
<BILL>
<BDATE>10/02/1999</BDATE>
<BTYPE>STANDARD</BTYPE>
<BILL_TOTAL>35.61</BILL_TOTAL>
<BALANCE>31.76</BALANCE>
</BILL>
</HISTORY>
</ACCOUNT>
<ACCOUNT>
<TYPE>Another</TYPE>
<ACCOUNT_NO>8888</ACCOUNT_NO>
<BALANCE>9.76</BALANCE>
<PRODUCTS>
<PRODUCT>
<TYPE>NORMAL</TYPE> <START_DATE>10/12/2003</START_DATE>
<END_DATE>23/01/2006</END_DATE>
<REFERENCE>d/4534535</REFERENCE>
<PAYMENT>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
</PAYMENT> </PRODUCT>
<PRODUCT>
<TYPE>NORMAL</TYPE> <START_DATE>10/12/2003</START_DATE>
<END_DATE>23/01/2006</END_DATE>
<REFERENCE>d/4534535</REFERENCE>
<PAYMENT>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
<PAY>
<PDATE>23/01/2004</PDATE>
<PTYPE>Routine</PTYPE>
<VALUE>2248</VALUE>
</PAY>
</PAYMENT>
</PRODUCT>
</PRODUCTS>
<HISTORY>
<BILL>
<BDATE>10/02/1999</BDATE>
<BTYPE>STANDARD</BTYPE>
<BILL_TOTAL>35.61</BILL_TOTAL>
<BALANCE>31.76</BALANCE>
</BILL>
<BILL>
<BDATE>10/02/1999</BDATE>
<BTYPE>STANDARD</BTYPE>
<BILL_TOTAL>35.61</BILL_TOTAL>
<BALANCE>31.76</BALANCE>
</BILL>
<BILL>
<BDATE>10/02/1999</BDATE>
<BTYPE>STANDARD</BTYPE>
<BILL_TOTAL>35.61</BILL_TOTAL>
<BALANCE>31.76</BALANCE>
</BILL>
</HISTORY>
</ACCOUNT>
</ACCOUNTS>
</PLACEMENTS>'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/PLACEMENTS/ACCOUNTS/ACCOUNT',1)
            WITH (AccountType  varchar(40) 'TYPE',
                  AccountNo varchar(40) 'ACCOUNT_NO')
SELECT    *
FROM       OPENXML (@idoc, '/PLACEMENTS/ACCOUNTS/ACCOUNT/PRODUCTS/PRODUCT',1)
            WITH (AccountNo varchar(40) '../../ACCOUNT_NO',
		  REFERENCE varchar(40) 'REFERENCE')
EXEC sp_xml_removedocument @idoc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources