SQL: Using XML as input to do an inner join

Question

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!

Answer 1

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:

• .nodes() must go down to the repeating element, which is <key>
• In .value() you are using the path @Key, which is wrong on two sides: 1) <key> is an element and not an attribute and 2) XML is strictly case-sensitive, so Key!=key.

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.

Answer 2

You probably need to parse the XML to a readable format with regex.

I wrote a similar event to parse the active DB from an xmlpayload that was saved on a table. This may or may not work for you, but you should be able to at least get started.

SELECT SUBSTRING(column FROM IF(locate('<key>',column)=0,0,0+LOCATE('<key>',column))) as KEY FROM table LIMIT 1\G