selecting just a portion of text with wildcard

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

selecting just a portion of text with wildcard

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-11-14 : 14:25:14

I have an xml document stored in a column. I would like to select just the text in one of the elements, regardless of what the text is. This is just for examination purposes, so speed is not an issue.

For instance, out of the document, I would want to select the following:
<disposition>first value</disposition>
<disposition>another value</disposition>

Thanks for any help.

oaleem
Starting Member

1 Post

Posted - 2008-11-14 : 17:31:36

Hey Chedder,

You can just get the XML values from within columns with XML data types using the following XQuery. It works on both typed and untyped XML.

To retrieve a specific element you can use the below Xquery..

SELECT Parent_Element.query('/Parent_Element/'Child_Element')
AS Result
FROM dbo.Table_name

You can also include the text() function to retrieve only the values within the Child Element... like so..

SELECT Parent_Element.query('/Parent_Element/'Child_Element/text()')

Hope it helps!

Thanks,
Omair

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-15 : 00:05:01

http://msdn.microsoft.com/en-us/library/ms345122.aspx

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-11-17 : 09:12:50

quote:
Originally posted by oaleem

Hey Chedder,

You can just get the XML values from within columns with XML data types using the following XQuery.

The column is a text data type. Will what you suggest still work? Can I do a cast or convert on it? I will play with it but further guidance would be appreciated.

Thanks for the help!

Matthew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 09:37:37

quote:
Originally posted by chedderslam

quote:
Originally posted by oaleem

Hey Chedder,

You can just get the XML values from within columns with XML data types using the following XQuery.

The column is a text data type. Will what you suggest still work? Can I do a cast or convert on it? I will play with it but further guidance would be appreciated.

Thanks for the help!

Matthew

if its text then you need is to use OPENXML

http://msdn.microsoft.com/en-us/library/aa276847(SQL.80).aspx

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-11-17 : 10:04:37