Solved

Parsing an XML field with TSQL

Posted on 2014-02-20
3
399 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
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 500 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to SQL Trace a SPECIFIC query 24 57
MS SQL - Rotating Values in SQL 9 50
How to identify differences between 2 backup files? 7 30
SSRS troubles 4 12
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now