March 5, 2008 at 6:30 am
Thank you
March 5, 2008 at 7:45 am
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
.
March 5, 2008 at 7:46 am
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
.
March 5, 2008 at 10:59 am
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.
March 5, 2008 at 11:55 am
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
March 5, 2008 at 12:58 pm
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
September 12, 2008 at 9:54 am
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
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy