Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Loading XML Data into SQL Server (SQL Spackle) Expand / Collapse
Author
Message
Posted Monday, January 3, 2011 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 10:55 AM
Points: 2, Visits: 32
gregg_dn (1/3/2011)
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


Yes, as the author noted, you have to provide an alias to the OpenRowset. In his example the author used rs. This will get you past that particular error. Good luck.
Post #1041929
Posted Monday, January 3, 2011 10:06 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:08 AM
Points: 754, Visits: 3,816
Excellent Wayne, thank you sir!


______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #1041932
Posted Monday, January 3, 2011 10:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:41 AM
Points: 210, Visits: 461
Thanks!
Post #1041935
Posted Monday, January 3, 2011 11:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, August 31, 2014 12:04 PM
Points: 6,594, Visits: 8,882
Kelsey Thornton (1/3/2011)
Thanks for this.
It's now clearer.


Kelsey,

Good, I'm glad it all makes sense. If it hadn't, I would have failed.


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 #1041993
Posted Monday, January 3, 2011 11:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, August 31, 2014 12:04 PM
Points: 6,594, Visits: 8,882
paul.marsh (1/3/2011)
decent article - good reference. but I feel a comment about the bcp utility should be added for those situations that warrant a different approach.


Paul - thanks. But how would you use BCP to load an XML file in? (This is what this article is about...)


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 #1041995
Posted Monday, January 3, 2011 12:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, August 31, 2014 12:04 PM
Points: 6,594, Visits: 8,882
shad-873858 (1/3/2011)
Yes, as the author noted, you have to provide an alias to the OpenRowset. In his example the author used rs. This will get you past that particular error. Good luck.


Shad - thanks for covering for me.


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 #1041996
Posted Monday, January 3, 2011 12:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, August 31, 2014 12:04 PM
Points: 6,594, Visits: 8,882
Jeff Moden (1/3/2011)
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!


You're welcome Jeff. Personally, I have to admit that it's pretty nice to be able to finally teach you something! (It's usually you teaching me! )


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 #1041999
Posted Monday, January 3, 2011 12:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, August 31, 2014 12:04 PM
Points: 6,594, Visits: 8,882
Steve Jones - SSC Editor (1/3/2011)
Excellent discussion, Wayne. This is one that I will keep bookmarked.
mtillman-921105 (1/3/2011)
Excellent Wayne, thank you sir!
gregg_dn (1/3/2011)
Thanks!


Steve, Matt and Gregg - thanks for your kind words, and I'm very glad that you'll found this article so nice.


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 #1042005
Posted Monday, January 3, 2011 1:46 PM


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: 2 days ago @ 4:26 AM
Points: 514, Visits: 1,728
This is great info. Thanks Wayne.

what do you think about making ALL x.data.values to be VARCHAR in the event that the XML is not well formatted?

Post #1042057
Posted Monday, January 3, 2011 2:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, August 31, 2014 12:04 PM
Points: 6,594, Visits: 8,882
Geoff A (1/3/2011)
This is great info. Thanks Wayne.

what do you think about making ALL x.data.values to be VARCHAR in the event that the XML is not well formatted?


For a standard way of doing things, I would not be in favour of doing that. Plus, it's not really XML if it's not well-formed, is it?


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 #1042066
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse