substring xml data

Question

substring xml data

tcronin 95651

SSCertifiable

Points: 6735
More actions
April 17, 2015 at 7:22 am

#398340

i have a table that has defined as xml. I would like to parse out one of the values, as a row by row I can figure this out, stuck on a regular select. Row by row looks like this
declare @inqid varchar(1000)
set @inqid = '<ExtendedInfo>
<PageCount>1631</PageCount>
<Fragmentation>10.2391</Fragmentation>
</ExtendedInfo>'
declare @EntryID varchar(100)
Declare @Start int, @End int, @Length int
select @Start = (CharIndex('<fragmentation>', @inqid) + Len('<fragmentation>'))
select @End = (CharIndex('</', @inqid, @Start))
Set @Length = @End - @Start
select @Length
select @EntryID = (Substring(@inqid, @Start, @Length))
Select @EntryID
What I have is a column called extendedeventinfo that holds the value I need to find
So
select extendedeventinfo, Substring to pull value out of table
from commandlog where extendedeventinfo is not null

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286589 More actions · Answer 1

tcronin 95651 (4/17/2015)
i have a table that has defined as xml. I would like to parse out one of the values, as a row by row I can figure this out, stuck on a regular select. Row by row looks like this
declare @inqid varchar(1000)
set @inqid = '<ExtendedInfo>
<PageCount>1631</PageCount>
<Fragmentation>10.2391</Fragmentation>
</ExtendedInfo>'
declare @EntryID varchar(100)
Declare @Start int, @End int, @Length int
select @Start = (CharIndex('<fragmentation>', @inqid) + Len('<fragmentation>'))
select @End = (CharIndex('</', @inqid, @Start))
Set @Length = @End - @Start
select @Length
select @EntryID = (Substring(@inqid, @Start, @Length))
Select @EntryID
What I have is a column called extendedeventinfo that holds the value I need to find
So
select extendedeventinfo, Substring to pull value out of table
from commandlog where extendedeventinfo is not null

What is the question here?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 2

sorry it was confusing

I can take a row by row like below

declare @inqid varchar(1000)

set @inqid = '<ExtendedInfo>

</ExtendedInfo>'

declare @EntryID varchar(100)

Declare @Start int, @End int, @Length int

select @Start = (CharIndex('<fragmentation>', @inqid) + Len('<fragmentation>'))

select @End = (CharIndex('</', @inqid, @Start))

Set @Length = @End - @Start

select @Length

select @EntryID = (Substring(@inqid, @Start, @Length))

Select @EntryID

This returns the value 10.2391 I want. Wanted to do this in one select for the whole table not row by row. The the table called commandlog has a column called extendedeventinfo that holds the value. i want a select against all the rows in the table so So

select *, Substring to pull value out of table (Struggling to get an inline substring to extract the value from the column called extendedeventinfo)

from commandlog where extendedeventinfo is not null

Sean Lange SSC Guru Points: 286589 More actions · Answer 3

You didn't make this much clearer so I am making some assumptions here. Please notice how I posted a table and sample data. This is something you should do in the future so we have something to work with and we know we are all working with the same thing.

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

inqid xml

)

insert #Something

select '<ExtendedInfo>

<PageCount>1631</PageCount>

<Fragmentation>10.2391</Fragmentation>

</ExtendedInfo>'

insert #Something

select '<ExtendedInfo>

<PageCount>1234</PageCount>

<Fragmentation>3.1415</Fragmentation>

</ExtendedInfo>'

select inqid.value('(/ExtendedInfo//Fragmentation/node())[1]', 'numeric(9,4)')

from #Something

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 4

thanks I was missing a paren in my soltution, probably could have looked at it for hours, my duh