Loading XML Data into SQL Server (SQL Spackle)

Question

Loading XML Data into SQL Server (SQL Spackle)

Viewing 15 posts - 16 through 30 (of 60 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

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
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 2

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! :w00t:)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 3

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!
:smooooth:

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
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Geoff A SSChampion Points: 11511 More actions · Answer 4

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?

WayneS SSC Guru Points: 95460 More actions · Answer 5

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
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

weharrelson Grasshopper Points: 23 More actions · Answer 6

FWIW, We have weekly imports of ~25MB of XML. The sp_xml_preparedocument method ran circles around the new ways (x.data.value...)

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 7

weharrelson (1/3/2011)
FWIW, We have weekly imports of ~25MB of XML. The sp_xml_preparedocument method ran circles around the new ways (x.data.value...)

Care to share the snippet that imports those weekly files?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 8

weharrelson (1/3/2011)
FWIW, We have weekly imports of ~25MB of XML. The sp_xml_preparedocument method ran circles around the new ways (x.data.value...)

BOL says that sp_xml_preparedocument needs to have the xml text passed in to it... so as Jeff brought up, how do you get them from a file to a variable?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Anil Maharjan SSC Veteran Points: 221 More actions · Answer 9

hello all,

Well i am just wondering do this similar type of script works for the complex type node too for xml file.

which we can find the XML file in link

http://www.w3schools.com/schema/el_complextype.asp

Thanks

Anil Maharjan

WayneS SSC Guru Points: 95460 More actions · Answer 10

Anil,

I'm not sure. The link that you gave only gives partial examples; if you have a sample XML that does this, we can try it. I'm not even sure if SQL will work with this.

I'd also suggest that you start a thread in the forums here for this, instead of tacking it on to this discussion of this article. You're more likely to get others involved to help you out.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 11

Anil Maharjan (1/4/2011)
hello all,
Well i am just wondering do this similar type of script works for the complex type node too for xml file.
which we can find the XML file in link
http://www.w3schools.com/schema/el_complextype.asp
Thanks
Anil Maharjan

In most cases I can think of, it does. (A limitation for example would be the re-declaration of the same namespace within a document or the like...)

But the link you provided is not an XML file. It's a schema definition that is used to validate an XML file.

So you might need to do as Wayne recommended: start a new thread and post a sample XML you'd like to shred.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Amy.G SSCrazy Points: 2305 More actions · Answer 12

This article is very helpful, Thank you.

Question: I was given a table in XML format that had a lot of web formatting included, such as:

- <div id="content">

- <div id="browseBox">

- <div class="licenseTitle">

- <h1>

<span id="_SE_FLD" _SE_FLD="tcm:Content/custom:Content/custom:Title[1]">Aboveground Storage Tank (AST) Permits (>1 Million Gallons Total)</span>

</h1>

- <div class="licenseDescription">

<span id="_SE_FLD" _SE_FLD="tcm:Content/custom:Content/custom:Description[1]">Minnesota Pollution Control Agency (MPCA)</span>

</div>

</div>

- <div id="licenseDetail">

- <div id="licenseRightColumn">

<h3 style="margin:0;line-height:100%;">Agency contact information:</h3>

- <p>

The table owner eventually sent me the data in Excel, but I've been wondering ever since if this is the type of XML data I SHOULD know how to work with. If so, how do you get around all those "round box" etc.?

LutzM SSC Guru Points: 107049 More actions · Answer 13

The sample data you provided aren't XML data. Those are HTML data.

As per http://www.w3schools.com/Xml/xml_whatis.asp

XML is not a replacement for HTML.
XML and HTML were designed with different goals:
* XML was designed to transport and store data, with focus on what data is
* HTML was designed to display data, with focus on how data looks
HTML is about displaying information, while XML is about carrying information.

Your sample mainly contains display information. Get the data in XML format (without the display information) and you're all set with Waynes article. 😉

Btw: what would be your expected output?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 14

Lutz,

Thanks for covering this!

With both HTML and XML looking somewhat similar, it's easy to see why one would get confused over it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Kelsey Thornton SSCrazy Points: 2157 More actions · Answer 15

LutzM (1/10/2011)
The sample data you provided aren't XML data. Those are HTML data.

Agreed, but they didn't say that this was the entire data file...

Amy.G (1/10/2011)
Question: I was given a table in XML format that had a lot of web formatting included, such as:...

(my emphasis)

If you read at the bottom of the post, then you might infer that the person was interpreting the HTML as XML, however...

As such, I think this is a valid question. If an XML file were to contain such data, then how would this T-SQL handle it?

Off the top of my head, I wouldn't expect T-SQL to treat HTML any differently from plain text...

Kelsey Thornton
MBCS CITP