April 29, 2012 at 8:41 am
I've tried watching videos, reading reference books, reading blogs, and searching this forum, and I still don't understand how to do what I need to do. Here is my XML document:
<?xml version="1.0" encoding="utf-8"?><MyRootElement>
<text>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Curabitur egestas suscipit lectus, et hendrerit nulla malesuada in. Aliquam ultricies felis.</text>
</MyRootElement>
I have over 31,000 <text> elements in the document inside the <MyRootElement>.
I managed to bulk insert the XML file into SQL Server 2012 Express into a table called XmlImportTest. If I do a SELECT * on the table I get two columns in my result set:
A column with the filename of my document, and a second column of type xml that seems to contain my entire document. If I click on the second column I get an error that there was an unexpected end of file (which makes me suspicious that the entire file did not load) and I cannot open the file in the XML Editor in SSMS.
That aside, what I need to do is insert the values inside the <text> tags into an existing column on another database table. The examples I have seen so far don't match the simplicity of my document (just containing a root element with 31,000+ <text> elements).
I need to understand how to do this, or if it's even possible to do inside SQL Server with my document in it's existing state. I get the feeling that since the entire document is in one column in the XmlImportTest table I cannot parse it and separate out the individual <text> element values into multiple rows in the same column in another table. I hope I'm wrong, but that is how it looks to me for now.
I tried using the nodes() method in XQuery but could not get any results. I'm stuck and don't know what I'm doing and so far haven't found anything that I can understand to help me.
Thanks.
April 30, 2012 at 8:18 am
Assuming your file DID load 100%, try a query like the following (you will have to fix the column name since you didn't specify what yours is):
select n.value('(./text())[1]','varchar(200)')
from XmlImportTest
cross apply xmlcol.nodes('/MyRootElement/text') x(n)
This should return a data set with each <text> node as its own record.
----------------------------------------------------------------------------------
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?
April 30, 2012 at 8:34 am
Another thing you can usually do is use OpenRowset to query the file directly, without importing it into a staging table first.
On the file terminating unexpectedly, how big is it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 30, 2012 at 12:52 pm
I'm not at home right now, but I believe the file was about 5 Mb.
May 1, 2012 at 6:01 am
Maybe my file didn't load completely (it was only about 5Mb) because when I used this code I got an empty unnamed column with no data in it in my result set, and the file I bulk uploaded had 31000+ rows of text data.
May 1, 2012 at 6:19 am
Try this, see what you get:
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T ;
CREATE TABLE #T (X XML) ;
INSERT INTO #T
(X)
VALUES ('<?xml version="1.0" encoding="utf-8"?>
<MyRootElement>
<text>1Lorem ipsum dolor sit amet, consectetur adipiscing elit. Curabitur egestas suscipit lectus, et hendrerit nulla malesuada in. Aliquam ultricies felis.</text>
<text>2Lorem ipsum dolor sit amet, consectetur adipiscing elit. Curabitur egestas suscipit lectus, et hendrerit nulla malesuada in. Aliquam ultricies felis.</text>
<text>3Lorem ipsum dolor sit amet, consectetur adipiscing elit. Curabitur egestas suscipit lectus, et hendrerit nulla malesuada in. Aliquam ultricies felis.</text>
</MyRootElement>') ;
SELECT X.N.value('(./text())[1]', 'varchar(1000)')
FROM #T
CROSS APPLY X.nodes('/MyRootElement/text') X (N) ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply