Solved

Import XML data into MS SQL 2000

Posted on 2008-09-30
8
2,272 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
Comment Utility
>>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
Comment Utility
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
Comment Utility
Have you considered using OPENXML?
0
 

Author Comment

by:TravelSmith
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for your help on this.  Your answer was not what I was looking for, but it will work.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now