Post reply

Getting XML in a table format

  • June 25, 2009 at 10:16 pm

    #135746

    Hi,

    I am trying to get an XML column in a table format. I take the XML value into a variable:

    DECLARE @idoc int

    DECLARE @doc NVARCHAR(MAX)

    SET @doc = (SELECT TOP 1 CONVERT(nvarchar(max), cast( message_body as xml )) FROM syseventqueue)

    SELECT @doc

    -------------------------------------------

    BLOCKED_PROCESS_REPORT...

    (1 row(s) affected)

    and get the above output, showing the value exists.

    When I try to convert it into table format:

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

    SELECT * FROM OPENXML (@idoc, '/ROOT/EVENT_INSTANCE',1)

    --------------------------------------------

    id,parentid,nodetype,localname,prefix,namespaceuri,datatype,prev,text

    (0 row(s) affected)

    I get not output.

    What is wrong with my code?

    Thanks.

    Just realised, I should have put this under SQL Server 2008 forum, because I am trying this on SQL Server 2008.

  • June 26, 2009 at 8:03 am

    #1016041

    It might be Node level mismatch. change the value 1 to 2 and try...

    [font="Verdana"]SELECT * FROM OPENXML (@idoc, '/ROOT/EVENT_INSTANCE',2)[/font]

  • June 28, 2009 at 4:54 pm

    #1016567

    No, tried that already. Would not work.

  • June 29, 2009 at 5:40 am

    #1016736

    Can U post your XML data ?

  • June 29, 2009 at 1:01 pm

    #1017140

    Why don't you use SQL Server XML functions instead of OPENXML?

    I believe performance is beter as well.

    http://www.mssqltips.com/tip.asp?tip=1609

    If you want a more specific answer, you need to post your XML.

  • June 29, 2009 at 6:58 pm

    #1017315

    Hi,

    this is the XML I am trying to render:

    BLOCKED_PROCESS_REPORT2009-06-25T22:20:52.7135

  • June 30, 2009 at 3:35 pm

    #1018023

    Hi Roust_m,

    to begin with the easy stuff: your xml is not well formed. the following closing tags are missing either at the end of your xml or in between:

    I just assume for the moment that it's caused by the way you provided the sample data... (Probably just copied the first few lines out of a larger xml file...?)

    It looks like you're trying to install a report on the BLOCKED_PROCESS_REPORT event.

    Maybe you should have a look at the following link which describes usage of the event above including data selection.

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx


    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]

  • June 30, 2009 at 6:09 pm

    #1018081

    This is exactly the article I read before setting the whole thing up. The only problem I have is not being able to retrieve the XML data in a form of a table and the article says nothing about it.

    BTW, this is the forum tag which cuts my XML text. I don't seem to be able to fit it inside the tag and I don't seem to be able to have to or more of them to continue to post the XML text.

    This is the next part of the XML text:

  • June 30, 2009 at 7:07 pm

    #1018087

    Ok, here it comes:

  • June 30, 2009 at 8:45 pm

    #1018108

    try this:

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

    SELECT * FROM OPENXML (@idoc, '/EVENT_INSTANCE',1)

  • June 30, 2009 at 10:32 pm

    #1018139

    Yep, this worked. Thanks.

  • July 1, 2009 at 10:49 am

    #1018583

    Roust_m (6/30/2009)

    This is exactly the article I read before setting the whole thing up. The only problem I have is not being able to retrieve the XML data in a form of a table and the article says nothing about it.

    ...

    I'm a little confused regarding your statement...

    The article I mentioned in my previous post includes the following code to store the xml in a table variable (@msgs) and using XQuery select some of the values as an example to start with.

    DECLARE @msgs TABLE ( message_body xml not null,

    message_sequence_number int not null );

    RECEIVE message_body, message_sequence_number

    FROM syseventqueue

    INTO @msgs;

    SELECT message_body,

    DatabaseId = cast( message_body as xml ).value( '(/EVENT_INSTANCE/DatabaseID)[1]', 'int' ),

    Process = cast( message_body as xml ).query( '/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process' )

    FROM @msgs

    ORDER BY message_sequence_number

    Since I don't know what your expected result is supposed to look like I can't really help you to achieve it using XQuery instead of OPENXML.

    All I can recommend is to look into XQuery rather than Openxml since it provides a lot more options and also -from my experience- is faster in most of the cases I'm using it (the other business cases use just a few nodes, so a performance gain cannot really be measured) .

    Regarding posting XML you should use [ code="xml" ] tags (without blanks).


    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]

Viewing 12 posts - 1 through 11 (of 11 total)

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