Select specific info from XML field

  • SQL_dummy-431245

    SSC-Addicted

    Points: 426

    Hello,

    I have a table (InfoTable) with a field with XML info like this:

    <Status>

    <IdStatus>1<IdStatus>

    <NomStatus>Status 1<NomStatus>

    <IdStatus>2<IdStatus>

    <NomStatus>Status 2<NomStatus>

    <IdStatus>3<IdStatus>

    <NomStatus>Status 3<NomStatus>

    ....

    </Status>

    <Process>

    <IdProcess>1</IdProcess>

    <NomProcess>Process 1</NomProcess>

    <IdProcess>2</IdProcess>

    <NomProcess>Process 2</NomProcess>

    <IdProcess>3</IdProcess>

    <NomProcess>Process 3</NomProcess>

    ....

    </Process>

    I need to be able to do the equivalent of: "select NomProcess where IdProcess=3" and get "Process 3" , for instance.

    How can I do it?

    Thanks in advance!!

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    Quick note, this xml structure doesn't make things easier;-)

    😎

    Here is an example that should get you passed this hurdle

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @TXML XML = N'<Status><IdStatus>1</IdStatus>

    <NomStatus>Status 1</NomStatus>

    <IdStatus>2</IdStatus>

    <NomStatus>Status 2</NomStatus>

    <IdStatus>3</IdStatus>

    <NomStatus>Status 3</NomStatus>

    </Status>

    <Process>

    <IdProcess>1</IdProcess>

    <NomProcess>Process 1</NomProcess>

    <IdProcess>2</IdProcess>

    <NomProcess>Process 2</NomProcess>

    <IdProcess>3</IdProcess>

    <NomProcess>Process 3</NomProcess>

    </Process>'

    SELECT

    ROW_NUMBER() OVER

    (

    PARTITION BY (SELECT NULL)

    ORDER BY (SELECT NULL)

    ) AS PR_RID

    ,IDPROCESS.DATA.value('.[1]','INT')

    ,NOMPROCESS.DATA.value('.[1]','VARCHAR(25)')

    FROM @TXML.nodes('Process/IdProcess') as IDPROCESS(DATA)

    CROSS APPLY @TXML.nodes('Process/NomProcess') as NOMPROCESS(DATA)

    WHERE CONVERT(INT,RIGHT(NOMPROCESS.DATA.value('.[1]','VARCHAR(25)'),1),1) = IDPROCESS.DATA.value('.[1]','INT')

    Results

    PR_RID

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

    1 1 Process 1

    2 2 Process 2

    3 3 Process 3

  • SQL_dummy-431245

    SSC-Addicted

    Points: 426

    WOW!!!

    You are the best!!

    Thank you very very much!!!!

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    SQL_dummy-431245 (11/10/2014)


    WOW!!!

    You are the best!!

    Goes without saying:-D

    Thank you very very much!!!!

    Glad to help, just realize that this is a limited solution, only single digit joining between the different nodes, will break if number of nodes are greater than 9! Seriously suggest that the xml structure is revised if possible!

    😎

  • Arthur Olcot

    SSCertifiable

    Points: 6008

    Here is another method which gets the NomProcess node immediately following the IdProcess node of the value that you specify

    DECLARE @xml XML

    SET @xml = '<Status>

    <IdStatus>1</IdStatus>

    <NomStatus>Status 1</NomStatus>

    <IdStatus>2</IdStatus>

    <NomStatus>Status 2</NomStatus>

    <IdStatus>3</IdStatus>

    <NomStatus>Status 3</NomStatus>

    </Status>

    <Process>

    <IdProcess>3</IdProcess>

    <NomProcess>Process 3</NomProcess>

    <IdProcess>2</IdProcess>

    <NomProcess>Process 2</NomProcess>

    <IdProcess>1</IdProcess>

    <NomProcess>Process 1</NomProcess>

    </Process>

    '

    DECLARE @NodeValue INT = 3

    SELECT @xml.value('(/Process/NomProcess[ . >> (/Process/IdProcess[text() = sql:variable("@NodeValue")] )[1] ])[1]', 'VARCHAR(100)')

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

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