xml node values with their respective parent node in sql

Question

<ExecutionSequence>
    <property name="Id">1437</property>
    <property name="Name">seq</property>
        <ExecutionBlock>
            <property name="Name">Block1</property>
            <ExecutionCommand>
                <property name="Name">Cmd1</property>
            </ExecutionCommand>
            <ExecutionCommand>
                <property name="Name">Cmd2</property>
            </ExecutionCommand>
        </ExecutionBlock>
    <ExecutionBlock>
        <property name="Name">Block2</property>
        <ExecutionCommand>
            <property name="Name">Cmd1</property>
        </ExecutionCommand>
        <ExecutionCommand>
            <property name="Name">Cmd2</property>
        </ExecutionCommand>
    </ExecutionBlock>
</ExecutionSequence>

I want all the commnd names with their respective parent name

seq Block1 cmd1

seq Block1 cmd2

seq Block2 cmd1

seq Block2 cmd2

score 0 · Answer 1 · answered Oct 03 '13 at 17:18

select
    e.c.value('(property[@name="Name"]/text())[1]', 'nvarchar(max)') as Sequence,
    b.c.value('(property[@name="Name"]/text())[1]', 'nvarchar(max)') as Block,
    c.c.value('(property[@name="Name"]/text())[1]', 'nvarchar(max)') as Command
from @data.nodes('ExecutionSequence') as e(c)
    outer apply e.c.nodes('ExecutionBlock') as b(c)
    outer apply b.c.nodes('ExecutionCommand') as c(c)

sql fiddle demo

xml node values with their respective parent node in sql

1 Answers1