Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wayne Sheffield » Loading XML Data into SQL Server (SQL...
Add to briefcase
49 posts, Page 1 of 5
12345»»»

Loading XML Data into SQL Server (SQL Spackle) Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
WayneS
Posted Saturday, January 01, 2011 11:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:35 PM
Points: 6,369, Visits: 8,232
Comments posted to this topic are about the item Loading XML Data into SQL Server (SQL Spackle)

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1041547
Jeff Moden
Posted Monday, January 03, 2011 12:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 32,906, Visits: 26,792
I have to admit, I've not yet had to enter the realm of loading XML files. But now I know where to go for a quick reference. Thanks for filling in the cracks on this one, Wayne!

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1041735
Kelsey Thornton
Posted Monday, January 03, 2011 1:48 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 04, 2013 4:10 AM
Points: 532, Visits: 281
WayneS (1/1/2011)
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

It's a nice article. Can you explain why do you use the OpenRowset function to load the data into a staging table?


Kelsey Thornton
MBCS CITP
Post #1041738
SQL-DBA
Posted Monday, January 03, 2011 3:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 2:37 PM
Points: 73, Visits: 273
I wonder what others are doing about doing an example like this when the XML is from a web service. I run into that more often then XML files. I tend to write C# as a separate application, but are db pros accessing plain old XML web services in the CLR of SQL Server. I would like to see an example like that.
Post #1041753
WayneS
Posted Monday, January 03, 2011 7:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:35 PM
Points: 6,369, Visits: 8,232
Kelsey Thornton (1/3/2011)
WayneS (1/1/2011)
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

It's a nice article. Can you explain why do you use the OpenRowset function to load the data into a staging table?

Hi Kelsey,

Very good question. (I especially like the quote from my signature... ) In retrospect, I think I should have included this in the article. Thanks for bringing it up!

To answer your question - it's to load the entire XML file into one record into a table, where we can then work with it. The code in reference is:
OPENROWSET(BULK N'C:\SQL\cd_catalog.xml', SINGLE_BLOB)

Notice that we're using the "BULK" and "SINGLE_BLOB" options of OPENROWSET. From the OpenRowset BOL reference in the article, here is what these two options do:
BULK

Uses the BULK rowset provider for OPENROWSET to read data from a file.


SINGLE_BLOB

Returns the contents of data_file as a single-row, single-column rowset of type varbinary(max).


I'm sure that there are other ways to accomplish this (for instance, utilize the sp_OA procedures to start up Scripting.FileSystemObject, open the file, read it line by line, append what you just read into a variable, and when finished reading the file, close it and write the results to a table - and clean up all the objects that you had to create along the way). Currently, I believe that using OpenRowset with the BULK and SINGLE_BLOB options is going to be the easiest, and probably the fastest way.

A discussion of all the various ways to load an external file into a table would lead to an article of it's own - beyond the scope of this "SQL Spackle"! Therefore, I just used the quick/easy way to get the job done.

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1041845
WayneS
Posted Monday, January 03, 2011 8:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:35 PM
Points: 6,369, Visits: 8,232
SQL-DBA (1/3/2011)
I wonder what others are doing about doing an example like this when the XML is from a web service. I run into that more often then XML files. I tend to write C# as a separate application, but are db pros accessing plain old XML web services in the CLR of SQL Server. I would like to see an example like that.


I think that this link will give you an example.

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1041849
Kelsey Thornton
Posted Monday, January 03, 2011 8:21 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 04, 2013 4:10 AM
Points: 532, Visits: 281
Thanks for this.
It's now clearer.

Kelsey Thornton
MBCS CITP
Post #1041853
paul.marsh
Posted Monday, January 03, 2011 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 10, 2011 10:58 PM
Points: 1, Visits: 13
decent article - good reference. but I feel a comment about the bcp utility should be added for those situations that warrant a different approach.
Post #1041873
Steve Jones - SSC Editor
Posted Monday, January 03, 2011 8:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:26 PM
Points: 31,425, Visits: 13,738
Excellent discussion, Wayne. This is one that I will keep bookmarked.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1041875
gregg_dn
Posted Monday, January 03, 2011 9:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 7:21 AM
Points: 98, Visits: 225
I'm getting an the following error when I try to run the OPENROWSET function:

DECLARE @CD TABLE (XMLData XML);
INSERT INTO @CD
SELECT *
FROM OPENROWSET(BULK N'C:\SQL\cd_catalog.xml', SINGLE_BlOB)

Msg 491, Level 16, State 1, Line 5
A correlation name must be specified for the bulk rowset in the from clause.

Is there something in the code beyond SINGLE_BLOB) ?
Thanks
G. Daniels
Post #1041901
« Prev Topic | Next Topic »

Add to briefcase
49 posts, Page 1 of 5
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.