Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

TSQL Parsing XML that is in a field? 3

Status
Not open for further replies.

gbaughma

IS-IT--Management
Staff member
Nov 21, 2003
4,772
US
Is there a (simple, preferably) way to parse XML that is stored in a field in a recordset?

In other words, I have a table that has XML data in one of the fields, such as <UserName>Ralph</UserName><Whatever>Something else</Whatever>...

So, is there a way to essentially do a "Sub-query" of the field with the XML in it?

SO, something like:

SELECT Field1, Field2 { SELECT UserName FROM Field3 AS XML } AS Username

... something along those lines? (I know that query didn't make sense... but I'm just trying to get my point across.)

Thanks in advance. :)



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Sure, in SQL Server 2005 and up there are many methods to work with XML data. You can use xquery syntax to query XML data.

It's a complex topic, so you may want to read excellent tutorials by Jacob Sebastian starting from

go from the last link up and then back to previous pages.

PluralSight Learning Library
 
[tt] SELECT Field1,Field2,Field3.query('//UserName[1]/text()') AS UserName FROM [blue]TheTableInQuestion[/blue][/tt]
 
  • Thread starter
  • Moderator
  • #4
Thanks, Tsuji.... that example was all I needed to get me going.

Markros: Thank you for the link. :)



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top