Post reply

Help with OPEN XML

  • June 27, 2006 at 4:20 pm

    #172813

    I am having problem with this SELECT statment

    DECLARE @RetVal int, @Doc varchar(8000), @iDoc int

    SELECT @Doc = '

    <AuditTrail>

     <AuditDateTime>2006-04-20T09:24:55</AuditDateTime>

     <UserName>Unknown</UserName>

    <ChangesXml><Tickets>  <Ticket>

        <TicketId>554</TicketId>

        <ProjectName>Dispatch</ProjectName>

        <CreatorName />

        <ReportedBy>Steve</ReportedBy>

        <Status />

        <Severity />

        <Name>Backup Failed</Name>

        <Description />

        </Ticket></Tickets></ChangesXml></AuditTrail>

    '

    EXEC @RetVal = sp_xml_preparedocument @idoc OUTPUT, @doc

    SELECT *

    FROM OPENXML (@idoc, N'/AuditTrail/ChangesXML/Tickets/Ticket',2)

    with (TicketID int '@TicketID',

          AuditDateTime varchar (100) ..///@AuditDateTime  )

    I can't get the TicketID shown correctly.  I also having problem with the syntax to display the AuditDateTime.  Please advice.  Thanks.

  • June 27, 2006 at 4:53 pm

    #646249

    look like the correct syntax for AuditDateTime is ../../../../AuditDateTime.  However it still shows nothing. 

  • June 27, 2006 at 5:42 pm

    #646256

    i finally got it works.

  • August 1, 2006 at 1:59 pm

    #652645

    So what did you do to get it to work? Enquiring minds want to know.

  • August 1, 2006 at 2:22 pm

    #652652

    I don't know why but the column name TicketId is case-sensitive.  If I used TicketID I got null. 

    DECLARE @RetVal int, @Doc varchar(8000), @iDoc int

    SELECT @Doc = '

    <AuditTrail>

     <AuditDateTime>2006-04-20T09:24:55</AuditDateTime>

     <UserName>Unknown</UserName>

    <ChangesXml><Tickets>  <Ticket>

        <TicketId>554</TicketId>

        <ProjectName>Dispatch</ProjectName>

        <CreatorName />

        <ReportedBy>Steve</ReportedBy>

        <Status />

        <Severity />

        <Name>Backup Failed</Name>

        <Description />

        </Ticket></Tickets></ChangesXml></AuditTrail>

    '

    EXEC @RetVal = sp_xml_preparedocument @idoc OUTPUT, @doc

      SELECT * FROM OPENXML(@idoc, '/AuditTrail/ChangesXml/Tickets/Ticket', 2) WITH

      (TicketId varchar (10),

      ProjectName varchar (100),

      AuditDateTime varchar(80) '../../../AuditDateTime')

    EXEC sp_xml_removedocument @iDoc

  • August 1, 2006 at 3:36 pm

    #652684

    Thanks!

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

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