Solved

Import XML data into MS SQL 2000

Posted on 2008-09-30
8
2,292 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Want an individual results display div 8 47
Regarding Disk IO 3 49
Powershell Regex Replace Question 5 24
Trying to understand why my Index is so large 12 18
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

726 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