?
Solved

SQL OPENROWSET Import XML

Posted on 2014-02-12
4
Medium Priority
?
1,257 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
[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
  • 2
4 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 1000 total points
ID: 39852828
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 39853282
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
ID: 39853612
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
ID: 39854853
I believe the problem is malformed XML.
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

762 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