Using a Scalar Function that expects a Table Parameter inside an Apply block

TLDR: I have a scalar function, that expects a table-valued parameter and returns the XML representation of that table. Now I need to use this function in a larger Update statement, where the table-parameter of the function is generated by inner Select statements.

Hi,

We have an XML type definition, say [dbo].[XmlTestType], that looks like something this:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="Root">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="Element" minOccurs="0" maxOccurs="unbounded">
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element name="Content" type="xsd:string" minOccurs="0" maxOccurs="unbounded" />
                        </xsd:sequence>
                        <xsd:attribute name="Name" type="xsd:string" use="required"/>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

It basically represents a collection of Element-Content(s) mappings, which each Element (having a Name Attribute) containing N Elements (of type string).

We also have this Definition in Table-Type form:

CREATE TYPE [dbo].[TableTestType] AS TABLE
(
     [Element] VARCHAR(255) NOT NULL
    ,[Content] VARCHAR(255) NOT NULL
    ,PRIMARY KEY CLUSTERED ( [Element], [Content] )
);

So far so simple. We also have two functions, that can convert between the XML- and Table-representation of this data model:

A scalar function with a table parameter of type [dbo].[TableTestType], that returns a xml of type [dbo].[XmlTestType]. Let’s call this one [dbo].[ConvertTableToXml].
A table-valued function with a xml parameter of type [dbo].[XmlTestType], that returns a table of type [dbo].[TableTestType]. Let’s call this one [dbo].[ConvertXmlToTable].

We have a separate table, that uses the xml data type in one of its columns:

CREATE TABLE [dbo].[SomeTable]
(
    [UID] INT NOT NULL IDENTITY(1,1)
   ,[XmlData] XML (DOCUMENT [dbo].[XmlTestType]) NULL
);

Everything is working fine so far, but now I need to update the XmlData inside the Table in an Update statement. For example, to modify the XmlData of all rows, to include the values of a separate table. So what I would like to do is something like this:

DECLARE @newDataForSomeTable AS TABLE
(
     [FK_dbo__SomeTable__UID] INT NOT NULL
    ,[Element] VARCHAR(255) NOT NULL
    ,[Content] VARCHAR(255) NOT NULL
);
/* @newDataForSomeTable gets filled */

UPDATE [tbl] SET [tbl].[XmlData] = [calc].[XmlData]
FROM [dbo].[SomeTable] AS [tbl]
CROSS APPLY
(
    SELECT [dbo].[ConvertTableToXml](
        SELECT
             [currentState].[Element]
            ,[currentState].[Content]
        FROM [dbo].[ConvertXmlToTable]([tbl].[XmlData]) AS [currentState]
    
        UNION

        SELECT
             [newData].[Element]
            ,[newData].[Content]
        FROM @newDataForSomeTable AS [newData]
        WHERE [newData].[FK_dbo__SomeTable__UID] = [tbl].[UID]
    ) AS [XmlData]
) AS [calc]

Is anything like this possible? Meaning to call a scalar function, that expects a table-valued parameter, inside an Apply block, where the table-valued parameter is generated with inner select statements?

Right now I have no choice but to Cursor over the table, generate a table variable of type [dbo].[TableTestType] from the current state of the row, insert the missing entries into that variable, and convert the table back to XML; updating each row one by one.

Kind regards, Frank

Answer

[can I] call a scalar function, that expects a table-valued parameter, inside an Apply block

No. There is no way to construct a table-valued type in a SELECT. You must declare a variable of the table type and then INSERT it.

Alternatively you can embed the xml.nodes parsing logic that transforms the XML into a table into the body of the calling query.

Advertisement

Answer