Solved

SQL OPENROWSET Import XML

Posted on 2014-02-12
4
1,162 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
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
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.

867 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

16 Experts available now in Live!

Get 1:1 Help Now