Solved

SQL OPENROWSET Import XML

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

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 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