From XML to SQL 2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

From XML to SQL 2000

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-01-13 : 16:50:36

I am the designer of this entire import process. I code the SQL and the Excel VBA at will.

I have developed an Excel XP spreadsheet that produces a large (33KB) XML document in a string variable in VBA code. The XML string contains all the data in the spreadsheet. The XML was created by reading rectangles out of Excel worksheets, and writing them to the XML DOM.

My initial design expectation was to write a Sproc in SQL2K that accepted this XML as a parameter, then use OPENXML to import it into the tables. However, it appears that the maximum input is varchar (4000) unless I am missing something.

My question: What is the optimal way to proceed here? I am doing an import of the data.

~ Shaun Merrill
Seattle, WA

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-01-13 : 17:51:51

No you have to use text datatype and you should be fine...
If you can do this with Tab or comma delimited data and use DTS import you will be much happier with the performance...
Many of us find XML a worthless pile of $#!#$%!!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-13 : 21:02:37

I second the tab/comma delimited approach. You'll save space, time, have MUCH simpler code, and can use bcp or BULK INSERT to load data much faster than using XML. XML is the WORST format to use if you're in an environment you have total control over.

Since you can code Excel VBA at will, you can use it to define the rectangles as named ranges instead of exporting them. DTS can import named ranges directly. This would let you avoid having a separate file of exported data.

Dare I suggest you look at linked servers, OPENROWSET, or OPENDATASOURCE too? All of these let you query ODBC and OLE DB data sources directly from T-SQL, including Excel sheets.

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-01-14 : 14:15:32

I guess the reason I wanted to use XML is that we plan to upgrade the (monsterous) Excel front-end with a more modern approach. Nearly all modern (Microsoft) approaches use XML as the conduit for the datagram. As you can see, I thought I was being proactive with designing this in XML. I am a firm believer in totally separating the front-end design from the back-end design. I call that avoiding "nasty coupling."
In the future, perhaps I'll replace Excel with a DHTML website, or a client-server app. . .

I respect robvolk's ideas of alternate data conduits. The only stipulation is that this should all be launched from the Excel front-end. Excel VBA should be responsible for initiating the process of sending it's data rectangles to SQL Server, which sits waiting for Excel to knock on it's door.

The 4000 Excel clients are all remote, and not visible to a DTS package. They are able to log into SQL Server though, and launch a Sproc. So this may negate all of robvolk's suggestions. I have ADO 2.7 on the client side and must program it to push the data up to SQL. Perhaps a loop of INSERT INTO statements ... I dunno. That's why I asked the forum.

For the record, ClarkBaker, I like XML.

~ Shaun Merrill
Seattle, WA

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-14 : 14:23:01

AS Clark mentions, OPENXML accepts text as datatype - so one option is to create a proc which accepts an input param of text and processes the xml stream.

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-01-14 : 14:28:22

Very good, ehorn. 2^31 bytes should be enough size!

~ Shaun Merrill
Seattle, WA

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-01-18 : 19:29:37

Given the fact that I have ADO 2.7 at my disposal, what parameter type (colored red) should I use?

The error I'm getting now is:

Error #5: The parameter @XML is invalid: Parameter object is improperly defined.
Inconsistent or incomplete information was provided.

The Reference to ADODB is made to: Microsoft ActiveX Data Objects 2.7 Library (msado27.tlb)



Dim cmd             As ADODB.Command
Dim p               As ADODB.Parameter
Dim intRowsAffected As Integer

Set p = New ADODB.Parameter
With p
  .Type = adLongVarChar
  .value = MakeXMLFromSheet()
  .Name = "@XML"
End With
With cmd
    .ActiveConnection = cnn
    .CommandText = "[dbo].[stp_ImportXML]"
    .CommandType = adCmdStoredProc
    .Parameters.Append p
    .Execute RecordsAffected:=intRowsAffected
End With

Here is my SPROC In SQL:

CREATE   PROCEDURE dbo.stp_ImportXML
@XML TEXT
 AS
declare @idoc int
exec sp_xml_preparedocument @idoc OUTPUT, @xml
INSERT INTO DataSheetSummary 
( 
  Number, ReviewDate, Overall_Score
)
SELECT *
FROM OPENXML (@idoc, '/OperationsReview')
WITH (
      Number    	NVARCHAR(4)   'Number'
    , ReviewDate  	DATETIME      'ReviewDate' 
    , Overall_Score     DECIMAL(9,4)  'Overall_Score'
     )

I've tried replacing MakeXMLFromSheet() with a very simple XML fragment, and I get the same error. So it must be the parameter type that is wrong.

~ Shaun Merrill
Seattle, WA

Kristen
Test

22859 Posts

Posted - 2005-01-20 : 00:46:57

>> .Name = "@XML"

Drop the "@" [in the call from VB] and I think you'll be fine

Re: CSV/XML how would SMerrill handle embedded commas, TABs and linebreaks in the spreadsheet cells? I would use XML too - I could pass that by a schema to prevent users from putting non-numeric values where they shouldn't ("T.B.C." in a Price column is the one we typically fall over) etc.

I've got a link to some code that puts a complete Excel sheet into XML somewhere - shout if it might be of interest and I'll go look it up.

Krsten

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-01-20 : 17:38:19

Kristen:
I think Excel can simply Save As XML. But I'm not interested, thanks.

No, removing "@" doesn't work. The line that fails is the one that appends P into the parameters collection. Can someone please assure me that the SQL 'TEXT' datatype is correctly matched to the 'adLongVarChar' parameter type?

In response to your question, Excel is notorious for permitting bad data. The best you can do is hold the user's hand better using VBA code to prevent him or her from typing anything but numbers into that field. You can program custom data-entry forms in Excel also.

I am writing custom DOM code to write out my XML, so at that point, I could filter out the bad commas, TABs and linebreaks. But XML can tolerate a lot of that depending upon the xs:whiteSpace settings in the schema (collapse, preserve, replace).

<xs:element name="ManagerName">
 <xs:simpleType>
  <xs:restriction base="xs:string">
   <xs:whiteSpace value="preserve"/>
  </xs:restriction>
 </xs:simpleType>
</xs:element>

I hope that helps, Kristen. But I still need help with my VBA call.

~ Shaun Merrill
Seattle, WA

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-01-20 : 19:55:37

I Found my own answer.
To properly map a SQL TEXT Datatype, I determined I must use:

.Type = adVarChar
.Size = 2147483647

What an obscure constant!
(I had to go to VB.NET, which has automated ADO coding to get the exact answer.)
Thanks for your help, folks.

~ Shaun Merrill
Seattle, WA

Kristen
Test

22859 Posts

Posted - 2005-01-21 : 12:40:01

"I think Excel can simply Save As XML. But I'm not interested, thanks."

That wasn't what I was meaning, but not to worry.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources