0

I need to join the ProdutID from this Data Extension with another one from a different DE.

Is there a way to do that considering that I have multiple values/fields under one column?

enter image description here

5
  • You likely would have to use a subquery to parse out the XML into a table format for you to then reference in your main query. This would likely be a TON of processing. You may want to break this into multiple queries or steps. Commented Sep 2, 2020 at 15:31
  • thanks, would you be able to give me an example of a subquery please
    – Duicug
    Commented Sep 2, 2020 at 15:32
  • 1
    SELECT a.* FROM ( SELECT b.* FROM [de] b WHERE a.x = b.x ) a Commented Sep 2, 2020 at 17:21
  • @Gortonington what am I doing wrong? SELECT a.ProductId FROM ( SELECT b.OrderLines FROM [data extension name] b WHERE a.ProductId = b.OrderLines ) a
    – Duicug
    Commented Sep 3, 2020 at 10:50
  • you need to parse the xml in the subquery. Check out this post from SO (stackoverflow.com/questions/17352323/…) Commented Sep 3, 2020 at 11:53

1 Answer 1

0

Unfortunately, the SFMC SQL won't allow you to parse the XML.

Fortunately, you can brute-force your way by using SUBSTRING (if XML structure is static - more in MS T-SQL Docs) or CHARINDEX with LEFT/RIGHT (if it is dynamic - more in my SFMC SQL Docs) on the column with text. Use the function directly within JOIN/ON.

LEFT JOIN columnB b ON a.Value = SUBSTRING(b.Value, 5, 10)

It might, however, slow down your query. Be cautious of AutoKill.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .