XML Workshop XII - Parsing a delimited string

Question

Post reply

XML Workshop XII - Parsing a delimited string

Viewing 8 posts - 16 through 22 (of 22 total)

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

Argneka SSC Enthusiast Points: 104 More actions · Answer 1

Argneka

SSC Enthusiast

Points: 104

March 5, 2008 at 6:30 am

#785433

Thank you

jacob sebastian SSChampion Points: 11822 More actions · Answer 2

Are you looking for something like the following?

DECLARE @x XML

SET @x =

'

-- Total count of Nodes

DECLARE @max-2 INT, @i INT

SELECT

@max-2 = @x.query('

{ count(/Employees/Employee) }

').value('e[1]','int')

-- Set counter variable to 1

SET @i = 1

-- variable to store employee name

DECLARE @EmpName VARCHAR(10)

-- loop starts

WHILE @i <= @max-2 BEGIN

-- select "Name" to the variable

SELECT

@EmpName = x.value('Name[1]', 'VARCHAR(20)')

FROM

@x.nodes('/Employees/Employee[position()=sql:variable("@i")]')

e(x)

-- print the name

PRINT @EmpName

-- increment counter

SET @i = @i + 1

END

.

jacob sebastian SSChampion Points: 11822 More actions · Answer 3

aplogies for the bad formatting. the editor does not help me to format correctly. I must be missing something. And my xml data is also missing in the post. But I guess this will help you to get an idea about writing loop to process XML elements

.

Argneka SSC Enthusiast Points: 104 More actions · Answer 4

Hi,

Yes that may work. I was able to pass as a XML values as attributes. Then I just used a CURSOR - I know performance hog and this works OK. I will try your method as well.

Thanks,

Alan

PS: I will forward the final solution as soon as I get it.

Argneka SSC Enthusiast Points: 104 More actions · Answer 5

Hi again,

I have it working, but I could not figure this part out. Query Analyzer throws an exception for XQUERY on this line of code:

SELECT

@max-2 = @x.query('

{ count(/Employees/Employee) }

').value('e[1]','int')

Right now I am passing the count but would like to dynamically obtain based on your example.

Thanks,

Alan

Argneka SSC Enthusiast Points: 104 More actions · Answer 6

Hi,

This does exacally what I wanted it to do. Thanks for the insight. I have alot more work to do but the core is 100%.

declare @XmlList XML

declare @count int

declare @rows int

set @count=1

set @XMLList=' '

Declare @MasterObjectName varchar(50)

Declare @MasterColumnName varchar(50)

Declare @MasterProperty varchar(50)

Declare @MasterValue varchar(50)

declare @XmlCount varchar(3)

Declare @ObjectName varchar(50)

Declare @ColumnName varchar(50)

Declare @Property varchar(50)

Declare @Value varchar(50)

select @XmlCount=CONVERT(varchar,@XmlList.query('count(/Rows/Row/.)'))

set @rows=CONVERT(int,@XmlCount)

select @rows

WHILE(@count<=@rows)

BEGIN

Select @ObjectName=Items.List.value('@ObjectName[1]','varchar(50)'),

@ColumnName=Items.List.value('@ColumnName[1]','varchar(50)'),

@Property=Items.List.value('@Property[1]','varchar(50)'),

@Value=Items.List.value('@Value[1]','varchar(50)')

from @XmlList.nodes('//Rows/Row[position()=sql:variable("@count")]') as Items(List)

set @count=@count+1

select @ObjectName

select @ColumnName

select @Property

select @Value

END

Thanks,

Alan

jacob sebastian SSChampion Points: 11822 More actions · Answer 7

I see that the editor has deleted XML tags in my post. So the XQUERY that you asked me in the previous post should look like this: (i am replacing XML tages with [])

SELECT

@max-2 = @x.query('[e]

{ count(/Employees/Employee) }

[/e]').value('e[1]','int')

.

EWalter SSC Rookie Points: 39 More actions · Answer 8

I wrote a function very similar to this. I added some code to catch reserved characters ( ,")

CREATE FUNCTION [dbo].[split](

@sInputList VARCHAR(max),

@sDelimiter VARCHAR(50)

) RETURNS @List TABLE (item VARCHAR(max))

BEGIN

declare @x xml

select @x=convert(xml,coalesce(' '+

replace(

replace(replace(replace(@sInputList,' ','>'),'"','"')

,@sDelimiter,' '))

insert @List(item)

select T.x.value('.','varchar(max)') as items

from @x.nodes('/t') T(x);

RETURN

END