Post reply

Grab value from XML based on another column's value

  • October 4, 2010 at 8:08 am

    #97594

    I need a way to dynamically grab a value from an XML string based on a value in another column in my table. The end result should look like the table below:

    DECLARE @temp TABLE(

    ErrorText nvarchar(4000),

    Severity nvarchar(3),

    ErrorCol nvarchar(50),

    ErrorData XML,

    ErrorValue nvarchar(50)

    )

    Insert INTO @Temp

    select 'Field EnrolledFromDateID does not comply with validation rule' as ErrorText, 'E' as Severity, 'EnrolledFromDateID' as ErrorCol, '<root><DW_Id>1</DW_Id><EnrolledFromDateID>20090901</EnrolledFromDateID><AcademicYear>20092010</AcademicYear></root>' as ErrorData, '20090901' as ErrorValue

    UNION

    select 'Field AcademicYear does not comply with validation rule', 'W', 'AcademicYear', '<root><DW_Id>1</DW_Id><EnrolledFromDateID>20090901</EnrolledFromDateID><AcademicYear>20092010</AcademicYear></root>' as ErrorData, '20092010' as ErrorValue

    select * from @temp

    I've been doing a CROSS APPLY to my XML column, but I have to specify the node(?) name in my select statement. My query is below:

    DECLARE @temp TABLE(

    ErrorText nvarchar(4000),

    Severity nvarchar(3),

    ErrorCol nvarchar(50),

    ErrorData XML,

    ErrorValue nvarchar(50)

    )

    Insert INTO @Temp

    select 'Field EnrolledFromDateID does not comply with validation rule' as ErrorText, 'E' as Severity, 'EnrolledFromDateID' as ErrorCol, '<root><DW_Id>1</DW_Id><EnrolledFromDateID>20090901</EnrolledFromDateID><AcademicYear>20092010</AcademicYear></root>' as ErrorData, '20090901' as ErrorValue

    UNION

    select 'Field AcademicYear does not comply with validation rule', 'W', 'AcademicYear', '<root><DW_Id>1</DW_Id><EnrolledFromDateID>20090901</EnrolledFromDateID><AcademicYear>20092010</AcademicYear></root>' as ErrorData, '20092010' as ErrorValue

    select t.*, n.l.value('EnrolledFromDateID[1]','VARCHAR(20)') as DesiredValue from @temp t

    CROSS APPLY ErrorData.nodes('//root') n(l)

    Any suggestions? Thanks!

  • October 4, 2010 at 8:21 am

    #1231033

    select t.*, n.l.value('.','VARCHAR(20)') as DesiredValue

    from @temp t

    CROSS APPLY ErrorData.nodes('/root/*[local-name(.)=sql:column("ErrorCol")]') n(l)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • October 4, 2010 at 8:29 am

    #1231038

    That was exactly what I needed. Thanks, Mark!

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

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