CODEWITHSUNDEEP
sqlsql-serverxmlfunction
Andrey
Andrey

Reputation: 517

Select xml nodes directly from user-defined function

I have select from xml, which parses attributes as columns like this:

declare @xml xml
select @xml = xml_Function()

SELECT
    Tab.Col.value('@fieldOne','varchar(20)') AS fieldOne,
    Tab.Col.value('@fieldTwo','varchar(20)') AS fieldTwo
FROM  @xml.nodes('/row') Tab(Col)

Is it possible to make select directly from xml_Function without copying result into @xml variable?

Something like:

SELECT * 
FROM xml_Function().nodes('/row')

Upvotes: 2

Views: 595

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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;

Upvotes: 2

Related Questions