Post reply

Using a SQL database string as XML dataset

  • January 15, 2010 at 10:50 am

    #209142

    I have a database with a table that has a string that is formatted as XML.

    Something like: <Root><Node1>1</Node1></Root>

    And I want to use this string and pass it into my SSRS report, so that it is a dataset.

    What would be the best way to go about this? I know there is an XML data source, but that seems to be for files and web services, I'm not sure how I'd pass a query into this.

  • January 15, 2010 at 12:34 pm

    #1104404

    I would create a view on SQL Server side that transforms the xml data into a table and query the view.

    You could also use that statement directly inside SSRS to query the data but I think it would be more efficient to do it on SQL Server side.

    Something like the following:

    CREATE VIEW testxml as

    select distinct

    t.c.value('@lname[1]','varchar(30)') AS c1,

    t.c.value('@fname[1]','varchar(30)') c2,

    t.c.value('Phone[1]','varchar(30)') c3,

    t.c.value('Address[1]','varchar(60)') c4

    from xmldata

    CROSS apply

    data.nodes('MyXML/Books/BookType/BooksOnType/Author') as t(c)


    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 18, 2010 at 3:47 am

    #1104847

    Thanks, this looks like it'll work 🙂

  • January 19, 2010 at 3:27 am

    #1105301

    Upon receiving more detail requirements, it seems like this may not work after all... the database is currently SQL Server 2000, there is no XML Data Type, which I think was required to use the Nodes method. 🙁

    Does anyone have any good options for when the XML in the database has to be stored as an ntext field?

    The database may be upgraded to 2005 at a later date, although the XML containing column MUST stay as an ntext, and we want to use this as the source (no scripts that mirrors the table in another location, then queries out of this, or anything along those lines.)

    edit: never mind, the answer is straightforward, as long as I still have access to the datatype - made a variable in the stored proc with the data type, then passed in the string to this, then used the same process as before to query out the information.

Viewing 4 posts - 1 through 3 (of 3 total)

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