sql-serverxmlt-sqlinner-joinshred

SQL: Using XML as input to do an inner join


I have XML coming in as the input, but I'm unclear on how I need to setup the data and statement to get the values from it. My XML is as follows:

<Keys>
    <key>246</key>
    <key>247</key>
    <key>248</key>
</Keys>

And I want to do the following (is simplified to get my point across)

Select *
From Transaction as t
Inner Join @InputXml.nodes('Keys') as K(X)
    on K.X.value('@Key', 'INT') = t.financial_transaction_grp_key

Can anyone provide how I would do that? What would my 3rd/4th line in the SQL look like? Thanks!


Solution

  • From your code I assume this is SQL-Server but you added the tag [mysql]...
    For your next question please keep in mind, that it is very important to know your tools (vendor and version).

    Assuming T-SQL and [sql-server] (according to the provided sample code) you were close:

    DECLARE @InputXml XML=
    N'<Keys>
        <key>246</key>
        <key>247</key>
        <key>248</key>
    </Keys>';
    
    DECLARE @YourTransactionTable TABLE(ID INT IDENTITY,financial_transaction_grp_key INT);
    INSERT INTO @YourTransactionTable VALUES (200),(246),(247),(300);
    
    Select t.*
    From @YourTransactionTable as t
    Inner Join @InputXml.nodes('/Keys/key') as K(X)
        on K.X.value('text()[1]', 'INT') = t.financial_transaction_grp_key;
    

    What was wrong:

    An alternative might be this:

    WHERE @InputXml.exist('/Keys/key[. cast as xs:int? = sql:column("financial_transaction_grp_key")]')=1;
    

    Which one is faster depends on the count of rows in your source table as well as the count of keys in your XML. Just try it out.