How do i iteate through each xml tag? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How do i iteate through each xml tag?

Author

Topic

petre
Starting Member

11 Posts

Posted - 2010-09-07 : 05:04:49

hi friends,

i have the following tabel-value fucntion tha i have created to accept a xml parameter as an arguement and return a table:

if exists (select 1 from sys.sysobjects where type = 'TF' and name = 'fn_get_int_table_frm_xml')
drop function fn_get_int_table_frm_xml
go

create function fn_get_int_table_frm_xml ( @xml_values xml )
returns @indexes table (idx int identity(1, 1) primary key, usr_id int, Role_id int)
as
begin
declare @ret xml
declare @rle_id, @usr_id int
set @ret = @xml_values
select UserID, RoleID from openxml('@ret', 'Roles/Role');

-- how to insert each child element to the @indexes table?

return
end
go

The XML parameter contains the following:
<Roles>
<Role>
<UserID>10</UserID>
<RoleID>200</RoleID>
</Role>
<Role>
<UserID>10</UserID>
<RoleID>300</RoleID>
</Role>
<Role>
<UserID>10</UserID>
<RoleID>400</RoleID>
</Role>
</Roles>

How do i extract each UserID and ROleID in each Role and insert to the virtual table @Indexes?

and please explain of methods that you call and values that you pass

thanks

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-07 : 05:39:12

Try this -


DECLARE @InXmlDoc AS xml
SET @InXmlDoc = 
'<Roles>
<Role>
<UserID>10</UserID>
<RoleID>200</RoleID>
</Role>
<Role>
<UserID>10</UserID>
<RoleID>300</RoleID>
</Role>
<Role>
<UserID>10</UserID>
<RoleID>400</RoleID>
</Role>
</Roles>'


SELECT  fileds.value('UserID[1]', 'varchar(30)') AS UserID,
		fileds.value('RoleID[1]', 'varchar(30)') AS RoleID
FROM	@InXmlDoc.nodes('//Roles/Role') as  xmldata(fileds)

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources