XML in DTS?

[fredong]

Hi All,
I have a xml document to import and export and how do I go about using the DTS to import the XML file as well as export it? BTW I am new to XML.I do not know how to call the XML file and data and insert it into SQLserver or vice versa. Any example will be greatly appreciated.Thanks.

 

[nice95gle]

I've been using this for a while...Enjoy

http://www.perfectxml.com/articles/xml/importxmlsql.asp

Well Done is better than well said
- Ben Franklin

 

[fredong]

Hi Nice95gle,
Thanks for your help. This site is a good site. However, on the site it has stated to look for the c:\Books.xml and c:\Books.xsd files (discussed above) saved on your hard disk. I found the c:\Books.xml but not the c:\Books.xsd, do you know where is it located? I am working on the Title
'DTS ActiveX Script & SQLXML 3.0 Bulk Load'. Please advise. Thanks.

 

[nice95gle]

The books.xsd is just a Schema file; it might not work for what you are doing. In other words it's just a Definition doc similar to the way you might use a css file. Use the posted code for books.xsd to get you started on building your own file.

<xsd:schema xmlns:xsd="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema"[/URL]

            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
 
  <xsd:element name="Book" sql:relation="tblBooks" >

   <xsd:complexType>

     <xsd:sequence>

       <xsd:element name="Title" type="xsd:string" sql:field="BookTitle" />

       <xsd:element name="Publisher" type="xsd:string" />

       <xsd:element name="DateOfPurchase" type="xsd:date" />

     </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

</xsd:schema>

If you need a little more help, do a google on XSD. And before I forget look up 'FOR XML' in Books Online. That might give you a quicker answer for what you are trying to do.

Well Done is better than well said
- Ben Franklin

 

[nice95gle]

Forgot to mention that you should use a text editor to build your file and save it with a .xsd extension.

example: books.xsd

Well Done is better than well said
- Ben Franklin

 

[fredong]

I am runnning a test of the example. So is it alright to use the books.xsd schema? Am I right? BTW what is the different between a CSS and xsd? Thanks.

 

[nice95gle]

By no means am I an expert on Web technology but a CSS file is a style file (Fonts, color, spacing, breakpoints). A XSD file is more of a structural file (fields, Null or non-null elements) basically the attributes of an XML doc.

And yes if you are just doing a test you can paste the code above into notepad and save as 'c:\books.xsd'

Well Done is better than well said
- Ben Franklin

 

[fredong]

Hi Nice95gle,
I executed the the Active X step and I ran into this error
Active X component can'tcreate object:'SQLXMLBulkLoad.SQLXMLBulkLoad.3.0' Do you know what is the cause of it? Thanks.

 

[nice95gle]

I think I know what the problem is, but before I jump to anything can you post your code?

Well Done is better than well said
- Ben Franklin

 

[fredong]

Here is my code


' Visual Basic ActiveX Script
'************************************************************************

Function Main()

Dim objXBulkLoad


Set objXBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")

objXBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB.1;SERVER= sqlTest ;UID= DTSTest; PWD= test; DATABASE= TESTDB;"

objXBulkLoad.KeepIdentity = False

'Optional Settings
objXBulkLoad.ErrorLogFile = "c:\NWError.LOG"
objXBulkLoad.TempFilePath = "c:\temp"

'Executing the bulk-load
objXBulkLoad.Execute "c:\books.xsd", "c:\books.xml"

Main = DTSTaskExecResult_Success

Main = DTSTaskExecResult_Success
End Function

 

[nice95gle]

Your code looks fine. You need to install SQLXML 3.0 sp3

http://www.microsoft.com/downloads/...54-8e23-47d2-a033-764259cfb53b&DisplayLang=en

Well Done is better than well said
- Ben Franklin

 

[fredong]

Hi nice95gle,
I created another test using global variable and code error out on "Object doesn't support this property or method: 'oCnn.Open'. Do you have any ideas? I hope this is the last question. Thanks.



'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()


Dim strServer
Dim strUserName
Dim strPassword
Dim oCnn
Dim sCnn


Set oCnn = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")

strServer = DTSGlobalVariables("strServerName").value
strUserName = DTSGlobalVariables("strSQLName").value
strPassword = DTSGlobalVariables("strSQLPwd").value
sCnn = "Provider=SQLOLEDB;Data Source=" + strServer + ";Initial Catalog=DTESTDB;User Id=" + strUserName + ";Password=" + strPassword + "; "


oCnn.Open sCnn ----> error out here

oCnn.KeepIdentity = False

'Optional Settings
oCnn.ErrorLogFile = "c:\NWError.LOG"
oCnn.TempFilePath = "c:\temp"

'Executing the bulk-load
oCnn.Execute "\C:\export\XML\Test\books.xsd", "C:\export\XML\Test\books.xml"

Main = DTSTaskExecResult_Success

Main = DTSTaskExecResult_Success
End Function

 

[nice95gle]

change oCnn.Open sCnn to oCnn.ConnectionString = sCnn

Well Done is better than well said
- Ben Franklin

 

[fredong]

Hi nice95gle,
That fix my problem. However, I executed the Active X I got another error on the books.xsd. The error stated below. My books.xsd codes is exactly what you have given me.Here is the error

"unable to load schema 'books.xsd'. An error ocurred(unKnown error)


P.S my tble created is exactly from the site you have given me

USE Northwind

GO



CREATE TABLE tblBooks(

Row_ID INTEGER IDENTITY(1,1) PRIMARY KEY,

BookTitle VARCHAR(100) NOT NULL,

Publisher VARCHAR(100) NULL,

DateOfPurchase DateTime NULL

)

GO



Thanks.

 

[nice95gle]

get rid of the \ in front of the C:\

oCnn.Execute "\C:\export\XML\Test\books.xsd", "C:\export\XML\Test\books.xml"

Well Done is better than well said
- Ben Franklin

 

[fredong]

Wow, I never even realized that there is a \ before the C:. Good Catch. That resolved this problem but I ran into another error on books.xsd.
------------------------------------------------------
Error source = Schema Mapping
Error Description =unable to load schema 'books.xsd'. An error ocurred(Incorrect document syntax) at line 1 column 1

Schema: unable to load schema 'books.xsd. An error ocurred(Incorrect document syntax) at line 1 column 1

------------------------------------------------------
below is the books.xsd code. I wonder if line 1 column 1 refers to the table or the http: ?



<xsd:schema xmlns:xsd="

http://www.w3.org/2001/XMLSchema"

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="Book" sql:relation="tblBooks" >

<xsd:complexType>

<xsd:sequence>

<xsd:element name="Title" type="xsd:string" sql:field="BookTitle" />

<xsd:element name="Publisher" type="xsd:string" />

<xsd:element name="DateOfPurchase" type="xsd:date" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

 

[nice95gle]

Where is your table?
I noticed in your connection string that the Initial Catalog=DTESTDB but your table was created using Northwind.

These must match. One of two things;

Change Initial Catalog=Northwind
OR create the table on the DTESTDB database

Well Done is better than well said
- Ben Franklin

 

[fredong]

I do not have a Northwind DB my table was created on DTESTDB since I started this test.Any ideas, Why the books.xsd failed? Thanks.

 

[nice95gle]

I got home and tried to re-create that error and I did. If the every first character in the xsd file is not a < it will raise that error.

'Schema: unable to load schema 'books.xsd. An error ocurred(Incorrect document syntax) at line 1 column 1'

The very first line should read
<xsd:schema xmlns:xsd="

http://www.w3.org/2001/XMLSchema"

Check the file again for any characters before the < and remove them.

Well Done is better than well said
- Ben Franklin

 

[fredong]

Below is my Books.xsd codes and I don't see any different. Any ideas? Thanks.



<xsd:schema xmlns:xsd="

http://www.w3.org/2001/XMLSchema"

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="Book" sql:relation="tblBooks" >

<xsd:complexType>

<xsd:sequence>

<xsd:element name="Title" type="xsd:string" sql:field="BookTitle" />

<xsd:element name="Publisher" type="xsd:string" />

<xsd:element name="DateOfPurchase" type="xsd:date" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>