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
Solved

SQL OPENROWSET Import XML

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2012 clustering 9 11
SQL Recursion 6 18
SQL View / Qtry 3 10
SQL Database Restore 2008 R2 1 12
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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 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