February 9, 2016 at 11:38 pm
Hi,
I need to import data from xml to sql server. I tried to open the xml in browser but it is not showing anything! I tried using XML editor, XML Notepad but not able to see anything!
How to read the xml files
February 10, 2016 at 5:07 am
If you don't see anything when opening the file with XML tools, then I suggest that you first try using non-XML tools to visually inspect what you have. Can you open the XML file in a standard text editor such as Notepad? (Or Notepad++ if you have that installed). If you cannot open the file or do not see anything using those tools, you probably have a file that does not contain valid XML, or that is empty. (If you want to be fully sure, you can always try opening the file with a hex editor)
If it opens, check if it looks like valid XML. Depending on the length of the content, you can sometimes find out if the XML is invalid by copying the full content, pasting it in the T-SQL query below and looking at the resulting error message.
DECLARE @v-2 varchar(max);
SET @v-2 = '(paste here)';
SELECT CAST(@v AS xml);
February 10, 2016 at 8:28 am
It is opening in the notepad but not readable. So I can't able to copy the content
February 10, 2016 at 9:14 am
ramana3327 (2/10/2016)
It is opening in the notepad but not readable. So I can't able to copy the content
Then the file is not valid xml. Xml is always normally readable.
Check with the source of the file. Maybe a bit was dropped in transmission? Or maybe they used compression or encryption on the XML data before sending it your way?
February 10, 2016 at 10:44 am
Thank you.
How do we know it is encrypted or compressed?
When I try to edit with Notepad ++ I can see only one line
<?xml version="1.0" encoding="utf-16"?>
With Note pad also I can able to read the same line but looks there is something else additional but not able to read. When I open with XML editor it is just opening new browser nothing else
File size is 189KB. It might be too large so can't able to see.
February 10, 2016 at 11:58 am
ramana3327 (2/10/2016)
Thank you.How do we know it is encrypted or compressed?
Ask the people who created the file.
When I try to edit with Notepad ++ I can see only one line
<?xml version="1.0" encoding="utf-16"?>
In that case it is neither encrypted nor compressed. That first line is clear text and shows up in Notepad++, so the rest of the file should be cleartext as well. It surprises me that you see nothing at all in Notepad++.
With Note pad also I can able to read the same line but looks there is something else additional but not able to read. When I open with XML editor it is just opening new browser nothing else
File size is 189KB. It might be too large so can't able to see.
Notepad++ is able to open files that are much larger than this.
You might try looking at the file with a hex editor (but don't bother if you don't know how to interpret the results, then you'd just be looking at a pretty picture). But really, your best course of action right now is to contact whoever created the file for you and get them involved.
February 10, 2016 at 1:16 pm
When I tried again using notepad ++
I got the msg File is too big to be opened by Notepad ++
Down side I can see the length:225449251.
Any idea but the size from the properties is showing 180MB only. Do you think it compressed to reduce the size of the file to keep it in shared?
February 10, 2016 at 1:28 pm
APparently Notepad++ requires a lot of memory when using large files. Perhaps you can close a few programs and try again? Alternatively, you could try finding and downloading other tools that are created to read large files. In a quick search, I saw UltraEdit pop up a few times, but there are lots of similar tools available.
However, I will repeat my suggestion to contact whoever sent you that file and work with them on the issue. That is much more likely to resolve the issues then continuing the conversation here.
February 10, 2016 at 3:40 pm
Hugo Kornelis (2/10/2016)
APparently Notepad++ requires a lot of memory when using large files. Perhaps you can close a few programs and try again? Alternatively, you could try finding and downloading other tools that are created to read large files. In a quick search, I saw UltraEdit pop up a few times, but there are lots of similar tools available.However, I will repeat my suggestion to contact whoever sent you that file and work with them on the issue. That is much more likely to resolve the issues then continuing the conversation here.
I can vouch for UltraEdit. It isn't free, but well worth the price. It handles large files, Unicode and many other things that some text editors have problems with. A co-worker almost fell over when he saw that it handled EBCDIC, which isn't used much any more, but it's sure handy to have if you ever need it, which he did.
Hugo's right on here. Contact the owner of the file who provided it to you. If you can't read the text, it isn't native XML.
February 11, 2016 at 9:53 am
I'm a fan of TextPad[/url], they claim "It can handle file sizes up to the largest contiguous chunk of 32-bit virtual memory.". I've been using it for years, especially since BRIEF went away.
At $27US, it's a heck of a good deal. It has no problems reading files in Hex, which has been useful many times for me. And it supports RegEx in find/replace operations, also very handy.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
February 11, 2016 at 10:21 am
Yes.
Thank you all.
March 7, 2016 at 1:01 pm
I am not getting the info from the other team.
They said that is automated process (job). I am not sure how can I import that to SQL Server.
When I use
CREATE TABLE T1(XmlCol xml)
INSERT T1
SELECT CONVERT(xml, BulkColumn, 2) FROM
OPENROWSET(Bulk '\\servername\file\SQL\Inventory.xml', SINGLE_BLOB) [rowsetresults];
I got error XML Parsing: Line 2, character 72931969, illegal character
I found the link and tried
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CAST(CAST(1 AS NVARCHAR(4000)) AS XML),GETDATE()
FROM OPENROWSET(BULK '\\\servername\file\SQL\Inventory.xml', SINGLE_BLOB) AS x;
Looks 1 row inserted but not able to open that link
March 7, 2016 at 3:28 pm
by casting to an NVARCHAR(4000) you are truncating the column. as per the error your error is at position 72,391,969 (substantially larger than 4,000 characters). So your inbound XML file is at least 70MB, which means it has to be stored into a NVARCHAR(MAX) column if you plan to store the entire thing as a single BLOB.
First advice - use your favorite text editor and find our what extraneous character is at that particular position. I am assuming some kind of extended character which hasn't been appropriately XML encoded or escaped.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 8, 2016 at 11:58 am
I tried using nvarchar(max) and imported. But when I tried to open I am getting error out of memory exception.
I tried changing extension from xml to txt and opened using notepad++
I attached the image below what I can see
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply