Select xml nodes directly from user-defined function

As far as I know you cannot use an XML returning function directly with nodes(), but you might use a CTE or a "sub-select" to get it inlined:

CREATE FUNCTION dbo.ComeBackAsXML(@XmlText VARCHAR(MAX))
RETURNS XML
AS
BEGIN
    DECLARE @x XML=CAST(@XmlText AS 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>';
WITH GetXml AS (SELECT dbo.ComeBackAsXml(@xText) AS AsXml)
SELECT item.value('@id','int') AS ItemID
      ,a.value('@attr','varchar(max)') AS a_Attr
FROM GetXml
CROSS APPLY GetXml.AsXml.nodes('/root/item') AS One(item)
CROSS APPLY One.item.nodes('a') AS The(a);
GO

DROP FUNCTION dbo.ComeBackAsXML;
GO

This is the CTE as sub-select:

SELECT item.value('@id','int') AS ItemID
      ,a.value('@attr','varchar(max)') AS a_Attr
FROM (SELECT dbo.ComeBackAsXml(@xText)) AS GetXml(AsXml)
CROSS APPLY GetXml.AsXml.nodes('/root/item') AS One(item)
CROSS APPLY One.item.nodes('a') AS The(a);

If you want to get your function parameterizable you must be aware, that the things you write into XPath expressions - in almost all cases - are literals and therefore not parameterizable...

I assume, that you do not want a "general-all-purpose" XML function dealing with any unknown structure you might need. I assume that there is a given XML which is always the same and you just want to write your queries shorter:

You have several ways to go

• deliver a table and use WHERE
• pass in a parameter and use it with sql:parameter()
• build the statement dynamically (needs a stored procedure)

Here you'll find an example for each approach:

CREATE FUNCTION dbo.ParseXML(@xml XML)
RETURNS TABLE
AS
RETURN
SELECT item.value('@id','int') AS ItemID
      ,a.value('@attr','varchar(max)') AS a_Attr
FROM @xml.nodes('/root/item') AS One(item)
CROSS APPLY One.item.nodes('a') AS 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 * FROM dbo.ParseXML(@x)
WHERE ItemID=3
GO

ALTER FUNCTION dbo.ParseXML(@xml XML,@ItemID INT)
RETURNS TABLE
AS
RETURN
SELECT item.value('@id','int') AS ItemID
      ,a.value('@attr','varchar(max)') AS a_Attr
FROM @xml.nodes('/root/item[@id=sql:variable("@ItemID")]') AS One(item)
CROSS APPLY One.item.nodes('a') AS 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 * FROM dbo.ParseXML(@x,5);
GO

CREATE PROCEDURE dbo.ParseXMLDynamically(@xml XML,@ItemID INT)
AS
BEGIN
    DECLARE @SqlCmd NVARCHAR(MAX)=
'SELECT item.value(''@id'',''int'') AS ItemID
      ,a.value(''@attr'',''varchar(max)'') AS a_Attr
FROM @xml.nodes(''/root/item[@id=' +  CAST(@ItemID AS VARCHAR(100)) + ']'') AS One(item)
CROSS APPLY One.item.nodes(''a'') AS 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;