• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

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?
0
n f
Asked:
n f
1 Solution
 
orommCommented:
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

0
 
Brian CroweCommented:
SELECT MyField.value('(/MyConfigData/Current/MyInputConfig/@IO2xxxxx)[1]', 'VARCHAR(50)') AS IO2xxxxx,
   MyField.value('(/MyConfigData/Current/MyInputConfig/@IO3xxxxx)[1]', 'VARCHAR(50)') AS IO3xxxxx
FROM MyTable
0
 
n fAuthor Commented:
This worked perfectly. Thank you!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now