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!

score 0 · Answer 1 · answered Oct 24 '19 at 18:09

0

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

answered Oct 24 '19 at 18:09

Just_Aj

33
5

The usage of `.nodes()` and `.value()` point to SQL-Server's `T-SQL`... And - at least in my eyes - one should never read XML with any hand-written / parsing / regex what-so-ever... Out there are some very mighty and handsome engines... – Shnugo Oct 25 '19 at 09:14

score 0 · Accepted Answer · answered Oct 25 '19 at 09:12

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.

Thank you, this was helpful in getting to the correct solution. I ended up using that as the solution for this problem. — JTR, Oct 25 '19 at 14:36

SQL: Using XML as input to do an inner join

2 Answers2