SQL Server: querying from string xml value

Question

I need to join some XML data with relational data and to do it in a pure non-procedural SQL Server.

I may pass the desired xml as a (n)varchar or even lexical parameter.

Since all the examples I've come across declare a variable of XML type, I expected that inline casting to XML might help:

SELECT T.c.value('.','int') AS result  
FROM CAST(N'<Root><row id="1"><name>Larry</name><oflw>some text</oflw></row><row id="2"><name>moe</name></row><row id="3" /></Root>' AS XML).nodes('/Root/row/@id') T(c) 

But I'm getting an "Incorrect syntax" error. I'm from Oracle so might have missed something obvious.

Is it possible to get a result set from string representation of xml in this way? Any other options to join relational data with the external xml?

The actual task is to enrich the passed xml with with relational table data and return the result either as xml or result set.

I am restricted by:

1. High latency
2. Tag fn-bea:execute-sql (for those in the know)

The table has a field that exactly matches value is in xml The number of rows - up to n000, both in table and xml

Any other ideas are also welcome

Answer 1

Here is a conceptual example how to convert XML into rectangular (row and columns) format.

After that it is very easy to join the result set with any table(s).

The actual task is to enrich the passed xml with with relational table data and return the result either as xml or result set.

It is possible, but you need to be specific by providing a minimal reproducible example: ##1-4.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, city VARCHAR(20));
INSERT @tbl (city) VALUES
('Miami'),
('Orlando');

DECLARE @xml XML = 
N'<Root>
    <row id="1">
        <name>Larry</name>
        <oflw>some text</oflw>
    </row>
    <row id="2">
        <name>moe</name>
    </row>
    <row id="3"/>
</Root>';
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT c.value('@id','int') AS id  
        , c.value('(name/text())[1]','VARCHAR(20)') AS [name]
        , c.value('(oflw/text())[1]','VARCHAR(20)') AS oflw  
    FROM @xml.nodes('/Root/row') AS t(c)
)
SELECT rs.*, t.city
FROM rs LEFT OUTER JOIN @tbl AS t ON t.ID = rs.id; 

SQL #2

;WITH rs AS
(
    SELECT TRY_CAST(N'<Root>
            <row id="1">
                <name>Larry</name>
                <oflw>some text</oflw>
            </row>
            <row id="2">
                <name>moe</name>
            </row>
            <row id="3"/>
        </Root>' AS XML) AS xmldata
)
SELECT c.value('@id','int') AS id  
        , c.value('(name/text())[1]','VARCHAR(20)') AS [name]
        , c.value('(oflw/text())[1]','VARCHAR(20)') AS oflw  
FROM rs
CROSS APPLY xmldata.nodes('/Root/row') AS t(c);

Output

id	name	oflw	city
1	Larry	some text	Miami
2	moe	NULL	Orlando
3	NULL	NULL	NULL