Reputation: 153
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
Upvotes: 1
Views: 400
Reputation: 22321
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
Upvotes: 2