[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Import XML data into MS SQL 2000

Posted on 2008-09-30
8
Medium Priority
?
2,318 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 1500 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

649 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