0

What I'm trying to do is extract 'To' addresses from what appears to be XML type code (within a SQL database), in order to create a column containing just the email addresses, using SQL.

I have seen similar posts on your site but using Oracle.

An extract from the content of the field is below

toAddress;deliveryOptionEnum;deliveryOptionAddressSMTPArray;226;<value xsi:type="SOAP-ENC:Array" SOAP-      ENC:arrayType="bus:addressSMTP[2]">
    <item xsi:type="bus:addressSMTP">Blah@Blah.com</item>
    <item xsi:type="bus:addressSMTP">hi.d@hi.co.uk</item>
</value>

ccAddress;deliveryOptionEnum;deliveryOptionAddressSMTPArray;226;<value xsi:type="SOAP-ENC:Array" SOAP-      ENC:arrayType="bus:addressSMTP[2]">
    <item xsi:type="bus:addressSMTP">Blah1@Blah1.com</item>
    <item xsi:type="bus:addressSMTP">hi1.d@hi1.co.uk</item>
</value>

bccAddress;deliveryOptionEnum;deliveryOptionAddressSMTPArray;226;<value xsi:type="SOAP-ENC:Array" SOAP-     ENC:arrayType="bus:addressSMTP[2]">
    <item xsi:type="bus:addressSMTP">Blah2@Blah2.com</item>
    <item xsi:type="bus:addressSMTP">hi2.d@hi2.co.uk</item>
</value>

subject;deliveryOptionEnum;deliveryOptionString;69;<value xsi:type="xsd:string">Manager Email</value>

The result of the selection I would want is below

ID    | To Address
------+---------------    
47383 | Blah@Blah.com   
47383 | hi.d@hi.co.uk

Note I'm not interested in the 'ccAddress', the 'bccAddress' or the 'Subject', at this stage.

Any assistance would be greatly appreciated

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Simon
  • 1
  • 2
    It's unclear what you have tried. Where is your [mcve]? Show what you have tried and the results you get via an [edit]. –  Dec 12 '18 at 16:52
  • 1
    XML support is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Dec 12 '18 at 17:21
  • Looks like you are trying to convert XML data to SQL server table in MS SQL server management studio. If that's the case, may be take a look at this post https://stackoverflow.com/questions/3989395/convert-xml-to-table-sql-server/3989477#3989477 – KRM Dec 12 '18 at 18:16

0 Answers0