Solved

Import XML data into MS SQL 2000

Posted on 2008-09-30
8
2,288 Views
Last Modified: 2013-11-10
I have an XML file that contains transaction information from an outside vendor and need to create a style sheet to import it into MS SQL server 2000.  I found the following code to import XML data and it works well if you have your style sheet correct.

'VBScript to import XML to DB
Function Main()
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=ServerName;database=DBName;integrated security=SSPI"
objBL.ErrorLogFile = "c:\export\error.log"
objBL.KeepIdentity="False"
objBL.Execute "E:\DecisionMate\Schema.xml", "E:\DecisionMate\CheckoutReport.xml"
Set objBL = Nothing
      Main = DTSTaskExecResult_Success
End Function

These are the 2 tables that I have created to import into.

CREATE TABLE CATransactionInfo (
      [ID] INT IDENTITY,
      InvoiceNumber      INTEGER ,
      CheckoutDate       DATETIME,
      TotalInvoiceAmount MONEY)

CREATE TABLE CAShipment (
      InvoiceNumber      INTEGER,
      ShipmentNumber     INTEGER,
      ShipNameTitle      VARCHAR(20),
      ShipFirstName      VARCHAR(50),
      SKU                VARCHAR(20)
)

Can someone dirrect me as to what I am doing wrong in the style sheet?  I have gotten imports to work for the Transaction info, but can't seem to figure out how to link the Shipment info to it.

Thanks,
I have scaled down the XML for this example, but if the structure has been maintained.
 
<?xml version="1.0" encoding="iso-8859-1"?>
<CheckoutReport>
	<ReportId>36892</ReportId>
	<ReportPeriodStart>7/15/2003 12:00:00 AM (ET)</ReportPeriodStart>
	<ReportPeriodEnd>7/15/2003 11:00:00 AM (ET)</ReportPeriodEnd>
	<Version>2.0</Version>
	<PurchaseList>
		<BuyerPurchase>
			<TransactionInfo>
				<InvoiceNumber>5647151</InvoiceNumber>
				<CheckoutDate>07/15/2003 10:47:37 AM</CheckoutDate>
				<TotalInvoiceAmount>102.48</TotalInvoiceAmount>
			</TransactionInfo>
			<ShipmentList>
				<Shipment>
					<ShipmentNumber>1</ShipmentNumber>
					<ShipNameTitle>Dr.</ShipNameTitle>
					<ShipFirstName><![CDATA[Mark]]></ShipFirstName>
					<Items>
						<Item>
						<SKU><![CDATA[SCI1]]></SKU>
						</Item>
					</Items>
				</Shipment>
			</ShipmentList>
		</BuyerPurchase>
	</PurchaseList>
</CheckoutReport>
 
Here is the style sheet that I am trying to use and I can't get it to import.  Right now it returns the error: "The column 'ShipmentNumber' was definde in the schema, but does not exist in the database."
 
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
  <xsd:appinfo>
    <sql:relationship name="TransShip"
          parent="CATransactionInfo"
          parent-key="InvoiceNumber"
          child="CAShipment"
          child-key="InvoiceNumber" />
  </xsd:appinfo>
</xsd:annotation>
 
 
<xsd:element name="TransactionInfo" 
   sql:relation="CATransactionInfo" 
   sql:key-fields="InvoiceNumber" >
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="InvoiceNumber"       type="xsd:integer" />
      <xsd:element name="CheckoutDate"        type="xsd:date" />
      <xsd:element name="TotalInvoiceAmount"  type="xsd:string" />
      <xsd:element name="ShipmentNumber"      type="xsd:integer" 
			sql:relation="CAShipment" 
			sql:relationship="TransShip" />
      <xsd:element name="ShipNameTitle"       type="xsd:string"
			sql:relation="CAShipment" 
			sql:relationship="TransShip" />
      <xsd:element name="ShipFirstName"       type="xsd:string"
			sql:relation="CAShipment" 
			sql:relationship="TransShip" />
      <xsd:element name="SKU"       type="xsd:string"
			sql:relation="CAShipment" 
			sql:relationship="TransShip" />
    </xsd:sequence>
        <xsd:attribute name="InvoiceNumber" sql:field="InvoiceNumber" />
        <xsd:attribute name="CheckoutDate" sql:field="CheckoutDate" />
        <xsd:attribute name="TotalInvoiceAmount" sql:field="TotalInvoiceAmount" />
        <xsd:attribute name="ShipmentNumber" sql:field="ShipmentNumber" />
        <xsd:attribute name="ShipNameTitle" sql:field="ShipNameTitle" />
        <xsd:attribute name="ShipFirstName" sql:field="ShipFirstName" />
        <xsd:attribute name="SKU" sql:field="SKU" />
  </xsd:complexType>
</xsd:element>
</xsd:schema>

Open in new window

0
Comment
Question by:TravelSmith
  • 4
  • 4
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22610711
>>I have an XML file that contains transaction information from an outside vendor and need to create a style sheet to import it into MS SQL server 2000<<
Why do you want to use a stylesheet?  Why not import the values directly?
0
 

Author Comment

by:TravelSmith
ID: 22610747
Hello acperkins,
I am trying to import the values into SQL server and I can't figure out how to do it.  I know you can use style sheets to do this, but I just can't figure out the right syntax.  

I am open to all suggestions on how to import XML data into SQL server 2000.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22610857
Have you considered using OPENXML?
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:TravelSmith
ID: 22615909
I have also tried to use OPENXML, but how do I get around the size limitation.  All of the examples that I see load the XML into a @Var VARCHAR(8000) and that is no where near big enough to hold the XML file that I will be working with.

If someone could give me an example using OPENXML I would gladly use that instead.  This Code Snippet works great if my file was small, but it is way to big to fit into a VARCHAR(8000).

Thanks, for all your help so far.

DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
declare @idoc int
DROP TABLE #tempXML 
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
 
SET @FileName = 'E:\Export\CheckoutReport.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''
 
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML
-- SELECT @y = 95
SET @x = 0
WHILE @x <> @y
    BEGIN
        SET @x = @x + 1
        SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK = @x
    END
PRINT @FileContents
SELECT @FileContents as FileContents
 
exec sp_xml_preparedocument @idoc OUTPUT, @FileContents
-- SELECT stmt using OPENXML rowset provider
SELECT *
--FROM   OPENXML ('E:\Export\CheckoutReport.xml', '/CheckoutReport/PurchaseList/BuyerPurchase',2)
FROM   OPENXML (@idoc, '/CheckoutReport/PurchaseList/BuyerPurchase',2)
WITH (
  InvoiceNumber VARCHAR(50)  'TransactionInfo/InvoiceNumber',
  ShipmentNumber VARCHAR(50) 'ShipmentList/Shipment/ShipmentNumber',
  ShipNameTitle  VARCHAR(50) 'ShipmentList/Shipment/ShipNameTitle',
  LotTitle VARCHAR(50)       'ShipmentList/Shipment/Items/Item/LotTitle',
  SKU VARCHAR(20)            'ShipmentList/Shipment/Items/Item/SKU',
  SiteName VARCHAR(50)       'ShipmentList/Shipment/Items/Item/SiteName'
	      )
EXEC sp_xml_removedocument @idoc
 
--SELECT * FROM #tempXML
 
-- DROP TABLE #tempXML 

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 22620404
>>This Code Snippet works great if my file was small, but it is way to big to fit into a VARCHAR(8000).<<
You need to use VBScript to pass in the contents to an text parameter.  You can then use OPENXML to extract the values.

P.S.  As you can see what comes around goes around.
http://www.experts-exchange.com/Community_Support/General/Q_23176292.html
0
 

Author Comment

by:TravelSmith
ID: 22625856
I saw an example of what you are suggesting and yes I could probably make that work, but I will tell you it is a lot slower way of importing XML into SQL server.  Using the SQLXMLBulkLoad VBScript command is actually very fast, but I just can't figure out the proper syntax for the style sheet to make it work.  We currently use this command for a XML load that happens every day that is over 1.2 GB in size and it takes about 10 min.  I dont think that the XML file for this project will be that big, but it could be pretty good size so I don't want to limit myself.

Thanks again for your ideas,
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22630647
Fair enough.  I am afraid I have never had a need to use SQLXmlBulkLoad, so I am going to have to pass on this one.
0
 

Author Closing Comment

by:TravelSmith
ID: 31501660
Thanks for your help on this.  Your answer was not what I was looking for, but it will work.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question