[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Parsing an XML field with TSQL

Posted on 2014-02-20
3
Medium Priority
?
407 Views
Last Modified: 2014-02-20
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
Comment
Question by:n f
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 3

Expert Comment

by:oromm
ID: 39874528
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
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 39874585
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
 
LVL 1

Author Closing Comment

by:n f
ID: 39874662
This worked perfectly. Thank you!
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question