Solved

SQL OPENROWSET Import XML

Posted on 2014-02-12
4
1,135 Views
Last Modified: 2014-02-12
The below SQL 2005 T-SQL works except for when the xml file being imported gets too big.  By too big I mean about 50K bytes.  SQL documentation indicates the size limitation is 2 gig.  The query does not return an error only a blank column where the XML data should be.  The only difference between working and only returning a blank column is the size of the XML file.  
I do not know how to resolve the issue that appears to be a file size limitation?
----------------------------------------------------------------------
Drop Table T

CREATE TABLE T (IntCol int, XmlCol xml)
GO
INSERT INTO T(XmlCol)
SELECT * FROM OPENROWSET(
   BULK 'C:\EPD\EPD315\Bin\CMP\1004_201402101153.xml',
   SINGLE_BLOB) AS x

select * from T
0
Comment
Question by:dastaub
  • 2
4 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 250 total points
Comment Utility
there are only two reasons why this can happen, which I can remotely think of

1) the path "C:\EPD\EPD315\Bin\CMP\1004_201402101153.xml" is incorrect
2) the file 1004_201402101153.xml is not a valid XML, try to open this file in an IE or any browser and see if the browser can open this file without any errors.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
50K is "not big", it's small 50M would be medium, 500MB big ...

so, I don't think it's the size "per se", it must be something else; i have quickly tested on a 10MB file without any issues and quickly.

eventually you might need to update the XML components, but still wondering.
I don't think it's "access" permissions, that would raise concrete error message about the file not being accessible

the XML not being valid would be this error:
Msg 9400, Level 16, State 1, Line 1
XML parsing: line xxxx, character yyyy, unexpected end of input


hmmmm
0
 

Author Comment

by:dastaub
Comment Utility
Yes, it does not send an error and it successfully imports smaller files even though it probably has nothing to do with size.  The XML file does open in Internet Explorer and does display the XML data.
0
 

Author Comment

by:dastaub
Comment Utility
I believe the problem is malformed XML.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now