sql - Select xml nodes directly from user-defined function -

i have select xml, wich parse attributes columns this:

declare @xml xml select @xml = xml_function()  select     tab.col.value('@fieldone','varchar(20)') fieldone,     tab.col.value('@fieldtwo','varchar(20)') fieldtwo  @xml.nodes('/row') tab(col) 

is possible make select directly xml_function without copying result @xml variable?

something like:

select *  xml_function().nodes('/row') 

as far know cannot use xml returning function directly nodes(), might use cte or "sub-select" inlined:

create function dbo.comebackasxml(@xmltext varchar(max)) returns xml begin     declare @x xml=cast(@xmltext xml);     return @x; end go   declare @xtext varchar(max)= '<root>   <item id="3">     <a attr="test1" />     <a attr="test2" />     <a attr="test3" />   </item>   <item id="5">     <a attr="test1" />     <a attr="test2" />   </item> </root>'; getxml (select dbo.comebackasxml(@xtext) asxml) select item.value('@id','int') itemid       ,a.value('@attr','varchar(max)') a_attr getxml cross apply getxml.asxml.nodes('/root/item') one(item) cross apply one.item.nodes('a') the(a); go  drop function dbo.comebackasxml; go 

this cte sub-select:

select item.value('@id','int') itemid       ,a.value('@attr','varchar(max)') a_attr (select dbo.comebackasxml(@xtext)) getxml(asxml) cross apply getxml.asxml.nodes('/root/item') one(item) cross apply one.item.nodes('a') the(a); 

if want function parameterizable must aware, things write xpath expressions - in cases - literals , therefore not parameterizable...

i assume, not want "general-all-purpose" xml function dealing unknown structure might need. assume there given xml same , want write queries shorter:

you have several ways go

• deliver table , use where
• pass in parameter , use sql:parameter()
• build statement dynamically (needs stored procedure)

here you'll find example each approach:

create function dbo.parsexml(@xml xml) returns table return select item.value('@id','int') itemid       ,a.value('@attr','varchar(max)') a_attr @xml.nodes('/root/item') one(item) cross apply one.item.nodes('a') the(a); go  declare @x xml= '<root>   <item id="3">     <a attr="test1" />     <a attr="test2" />     <a attr="test3" />   </item>   <item id="5">     <a attr="test1" />     <a attr="test2" />   </item> </root>';  select * dbo.parsexml(@x) itemid=3 go  alter function dbo.parsexml(@xml xml,@itemid int) returns table return select item.value('@id','int') itemid       ,a.value('@attr','varchar(max)') a_attr @xml.nodes('/root/item[@id=sql:variable("@itemid")]') one(item) cross apply one.item.nodes('a') the(a); go  declare @x xml= '<root>   <item id="3">     <a attr="test1" />     <a attr="test2" />     <a attr="test3" />   </item>   <item id="5">     <a attr="test1" />     <a attr="test2" />   </item> </root>';  select * dbo.parsexml(@x,5); go  create procedure dbo.parsexmldynamically(@xml xml,@itemid int) begin     declare @sqlcmd nvarchar(max)= 'select item.value(''@id'',''int'') itemid       ,a.value(''@attr'',''varchar(max)'') a_attr @xml.nodes(''/root/item[@id=' +  cast(@itemid varchar(100)) + ']'') one(item) cross apply one.item.nodes(''a'') the(a)';  execute sp_executesql @sqlcmd,n'@xml xml',@xml; end go  declare @x xml= '<root>   <item id="3">     <a attr="test1" />     <a attr="test2" />     <a attr="test3" />   </item>   <item id="5">     <a attr="test1" />     <a attr="test2" />   </item> </root>';  exec dbo.parsexmldynamically @x, 5; go  drop procedure dbo.parsexmldynamically; drop function dbo.parsexml;