Need help with SQL XML Query

Question

I have almost no experience with querying XML data with T-SQL. Given the XML shown below, how would I extract the 'Sub_ID' value?

<MESSAGE LABEL = "IVSWEEP" >
    <H 
        SIMULATOR = "54355" 
        COMMENT = "" 
        OPERATOR = "Operator" 
        SUB_ID = "090617041199" 
        PROCESS_CD = "AHIPOT" 
        DATE_TIME = "6/19/2009 11:57:35 AM" 
        TMOD = "23.05" 
        TCOR = "23.05" 
        <D P = "0" I = "0" V = "0" />
        <D P = "1" I = "1.250854" V = "0" />
        <D P = "2" I = "1.251904" V = "4.212341" />
        <D P = "3" I = "1.24895" V = "7.517395" />
    </H>
</MESSAGE>

Answer 1

Here is one way. Your XML was invalid. Missing a > to terminate <H.

declare @xml xml = '<MESSAGE LABEL = "IVSWEEP" >
    <H 
        SIMULATOR = "54355" 
        COMMENT = "" 
        OPERATOR = "Operator" 
        SUB_ID = "090617041199" 
        PROCESS_CD = "AHIPOT" 
        DATE_TIME = "6/19/2009 11:57:35 AM" 
        TMOD = "23.05" 
        TCOR = "23.05"> 
        <D P = "0" I = "0" V = "0" />
        <D P = "1" I = "1.250854" V = "0" />
        <D P = "2" I = "1.251904" V = "4.212341" />
        <D P = "3" I = "1.24895" V = "7.517395" />
    </H>
</MESSAGE>'

declare @T table (Col1 xml)
insert into @T values(@xml)
insert into @T values(@xml)
insert into @T values(@xml)

select 
  m.h.value('@SUB_ID', 'varchar(50)')
from @T
  cross apply Col1.nodes('MESSAGE/H') as m(h)