Post reply

Loading XML Data into SQL Server (SQL Spackle)

  • January 3, 2011 at 12:00 pm

    #1268610

    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

  • January 3, 2011 at 12:05 pm

    #1268613

    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

  • January 3, 2011 at 12:10 pm

    #1268619

    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

  • January 3, 2011 at 1:46 pm

    #1268666

    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?

  • January 3, 2011 at 2:12 pm

    #1268673

    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

  • January 3, 2011 at 4:09 pm

    #1268698

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

  • January 3, 2011 at 6:41 pm

    #1268715

    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)
    Intro to Tally Tables and Functions

  • January 3, 2011 at 7:10 pm

    #1268718

    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

  • January 4, 2011 at 1:48 am

    #1268808

    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

  • January 4, 2011 at 9:38 am

    #1269060

    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

  • January 4, 2011 at 9:53 am

    #1269078

    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]

  • January 10, 2011 at 3:41 pm

    #1271650

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

    <a name="ada-content" />

    - <div id="browseBox">

    <div class="roundedBox_top-left" />

    <div class="roundedBox_top-right" />

    - <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 class="roundedBox_bottom-buff" />

    </div>

    <div class="roundedBox_bottom-left" />

    <div class="roundedBox_bottom-right" />

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

  • January 10, 2011 at 4:06 pm

    #1271655

    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]

  • January 10, 2011 at 6:19 pm

    #1271684

    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

  • January 11, 2011 at 1:00 am

    #1271744

    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

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

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