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>

Could you use Linq to XML instead? Here's one short example: hookedonlinq.com/LINQtoXML5MinuteOverview.ashx — DOK
– DOK, Commented Feb 22, 2011 at 16:27
@DOK - I wish, but not in this case. I already know how to do this in L2XML. I need to parse this from a stored proc. — Randy Minder
– Randy Minder, Commented Feb 22, 2011 at 16:28

Mikael Eriksson · Accepted Answer · 2011-02-22 16:51:54Z

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)

edited Feb 22, 2011 at 16:51

answered Feb 22, 2011 at 16:32

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Randy Minder Over a year ago

Thank you. I forgot to mention one thing. I am reading the XML values from rows in a table. How would I do this if I had to process many rows at a time, and retrieve the SUB_ID value from each row?

Mikael Eriksson Over a year ago

@Randy: Updated answer with a table version.

Collectives™ on Stack Overflow

Need help with SQL XML Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related