asked on

Parsing an XML field with TSQL

I have a table (MyTable) with an XML field (MyField). Sample data in MyField looks like:

<MyConfigData>
<Pending />
<Current>
<MyInputConfig IO1Enabled="true" IO2Enabled="true" IO3Enabled="true" IO1xxxxx="false" IO2xxxxx="false" IO3xxxxx="true" IO1yyyyy="1" IO2yyyyy="1" IO3yyyyy="0" IO1zzzzz="false" IO2zzzzz="true" IO3zzzzz="false" SentUTC="2010-06-01T18:23:50.12Z" Status="2" MessageSourceID="999999" />
</Current>
</MyConfigData>

I need to be able to query MyField to get the values for IO2xxxxx and IO3xxxxx but I can't seem to get these values out of the XML.

How do you query the XML to get these values out?

oromm

Try this:

declare @xml xml = N'<MyConfigData>
  <Pending />
  <Current>
    <MyInputConfig IO1Enabled="true" IO2Enabled="true" IO3Enabled="true" IO1xxxxx="false" IO2xxxxx="false" IO3xxxxx="true" IO1yyyyy="1" IO2yyyyy="1" IO3yyyyy="0" IO1zzzzz="false" IO2zzzzz="true" IO3zzzzz="false" SentUTC="2010-06-01T18:23:50.12Z" Status="2" MessageSourceID="999999" />
  </Current>
</MyConfigData>'
;
select [Current].Config.value('(MyInputConfig/@IO2xxxxx)[1]', 'varchar(10)') [IO2xxxxx]
 , [Current].Config.value('(MyInputConfig/@IO3xxxxx)[1]', 'varchar(10)') [IO3xxxxx]
 , [Current].Config.value('(MyInputConfig/@MessageSourceID)[1]', 'varchar(10)') [MessageSourceID]
from @xml.nodes('/MyConfigData/Current') as [Current](Config)
;

Open in new window

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

n f

ASKER

This worked perfectly. Thank you!