Limiting XML by ElementName - SQL Server Forums

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)

Limiting XML by ElementName

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-10-02 : 22:45:43

Im trying to filter XML by joining Element Name to a value in a row.

Here is the wrong way:


declare @x xml
set @x = '
<t>
	<One>val</One>
	<Two>val</Two>
	<Three>val</Three>
</t>
'

declare @t table (ElementName varchar(10))
insert into @t
	select 'One' union select 'Three'


declare @Return nvarchar(1000)

select	@Return = 
		coalesce(@Return + '', '') + 
		'<' + cast(c.query('fn:local-name(.)') as nvarchar(500)) + '>' + 
		cast(c.query('./text()') as nvarchar(500)) + 
		'</' + cast(c.query('fn:local-name(.)') as nvarchar(500)) + '>'
from	@x.nodes(N'//*') x(c)
inner
join	@t t on cast(c.query('fn:local-name(.)') as nvarchar(500)) = t.ElementName

select cast(@Return as xml)
for xml path('t'), type

Is there a way to act directly on the XML?

Thanks for any help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources