Join tables, between column and attribute in xml data

Question

I'm trying to make a query where I select all orders on a table, where the reference order number is the same as one of the values in an xml data type column. Since I'm using this in a much larger scale query i need in this case a LEFT JOIN.

So let's say I have :

Table 1 :       id   |    reference_number
                 1         1001
                 2         37904
Table 2 :     Guid                                    |     XMLData        |    Messagge
           A1625F87-9F6F-45FC-B2EE-06CE0FF8EDA0                                    OK
         
This is my xml :
<MyOrders>
          <Order>  
            <FACI>600</FACI>
            <CUOR>Test Email</CUOR>
            <OREF>37904</OREF> // this is the number i need
            <Customer>
              <M3CUNO>US00860</M3CUNO>
            </Customer>
            <OrderLine>
              <ITNO>360007</ITNO>
              <ORQT>1</ORQT>
              <WHLO>60S</WHLO>
              <SAPR>50.0000</SAPR>
            </OrderLine>
          </Order>
</MyOrders>    

Now I need to get the message field from table 2 and the id from table 1. The OREF in xml must be the same as reference_number in table1 This is what I tried but I can't get that value.

select dbo.Table2.Message, dbo.Table1.id
from dbo.Table1 soh 
left join dbo.Table2 aq
on aq.XMLData.nodes.value('/MyOrders/Order/OREF') = soh.reference_number

Ofcourse this gives me this error :

Remote function reference 'aq.XMLData.nodes.value' is not allowed, and the column name 'XMLData' could not be found or is ambiguous

Any help would be highly appreciated. Thank you in advance

Answer 1

You should always provide a minimal reproducible example.

Assuming that the reference_number column is INTEGER data type.

SQL

...
on aq.XMLData.value('(/MyOrders/Order/OREF/text())[1]', 'INT') = soh.reference_number

SQL Fiddle: T-SQL