Post reply

Manipulation on Excel generated XML file in sql server 2005

  • April 19, 2010 at 2:49 am

    #144116

    Hi,

    I have an excel generated XML file(automatically generated).

    I'm able to store this complete document on an XML datatype column in sqlserver 2005.

    But how do i go about doing manipulations on this XML column?

    Would be grateful for any pointers on the same

  • April 19, 2010 at 5:44 am

    #1153720

    Use the XMLcolumn.query syntax

    See XML Support in Microsoft SQL Server 2005

  • April 20, 2010 at 6:13 am

    #1154489

    Hi Tom,

    Thanks for the reply..But I see that the same is not working in the case of automatically generated XML as it contains namespace .

    Do I separately need to declare the namespace ?

  • April 20, 2010 at 12:32 pm

    #1154861

    Yes, you'd need to declare namespaces.

    There are several ways to shed some light on it:

    1) have a look at BOL (BooksOnLine), section WITH XMLNAMESPACES.

    2) search this site for "XML Workshop Sebastian Namespaces" to get a list of very helpful articles or

    3) post some sample data together with your expected result and somebody will show you how to do it based on your sample data.


    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]

  • April 21, 2010 at 1:28 am

    #1155143

    Thank u!!

    So my requirement is something like this.

    I have a no:of excel templates which needs to be stored in sql server 2005.Earlier these templates were stored in excel 2003.But now it is being migrated to Excel 2007.

    Manipulations have to be done (basically querying and retrieving data) have to be done on each of these templates.

    I'm attaching here a sample excel sheet (saved as type: XML Spread sheet) in Excel 2003

    (But as sqlservercentral.com is not allowing me to upload an XML file I will attach the .XLS version of the same.)

    When I open this .XML file in a notepad, I get a well formed XML data which I'm able to save as a complete document in sql server 2005. (Syntax attached)

    But I'm having trouble traversing to a particular node in this XML document.

    Also in the excel sheet if I need to retrieve all Id's where values is > 2000 , how do I go about it?

  • April 21, 2010 at 1:53 pm

    #1155644

    Your requirement is a little strange...

    Why do you want to query the XML version of an Excel file? Wouldn't it be a lot easier to use OPENROWSET to the Excel doc?

    Anyway, if you really need to deal with Excel in xml format, here's how you'd get the desired output (at least it's one version of it, I'm not sure if it's the best performing).

    I'm using XMLNAMESPACES to declare the default namespace I want to use ("ss") together with a WHERE clause to eliminate the first row (header).

    ;WITH xmlnamespaces (

    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet'

    )

    SELECT

    c.value('Cell[1]','VARCHAR(30)') c1,

    c.value('Cell[2]','VARCHAR(30)') c2

    FROM

    test_xml

    CROSS APPLY

    xml_doc.nodes('/Workbook/Worksheet/Table/Row') T(c)

    WHERE

    CASE

    WHEN c.value('Cell[2]','VARCHAR(30)')='values'

    THEN 0

    ELSE c.value('Cell[2]','VARCHAR(30)')

    END > 2000


    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]

  • May 14, 2010 at 8:06 am

    #1167555

    Thank you so much Lutz!!

    can you also pls let me know what differnce I have to make if it is sheet 2 of the same excel sheet?(ie,, retrieve data from different tabs)

Viewing 7 posts - 1 through 6 (of 6 total)

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