Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2372
  • Last Modified:

Import XML data into MS SQL 2000

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
TravelSmith
Asked:
TravelSmith
  • 4
  • 4
1 Solution
 
Anthony PerkinsCommented:
>>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
 
TravelSmithAuthor Commented:
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
 
Anthony PerkinsCommented:
Have you considered using OPENXML?
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
TravelSmithAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
TravelSmithAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
TravelSmithAuthor Commented:
Thanks for your help on this.  Your answer was not what I was looking for, but it will work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now