Converting XML Data from SQL Data Field

I apologize as we are new to this product but learning quickly.

I have a SQL data source that returns two fields - one is the key for the values and the other is the actual value field.

Our issue is that the original data in the value field is stored in a SQL Server varchar field but actually contains XML formatted data.

Example:
Enitity Field = 30

Value Field =
<Attributes>
<CustomerAttribute ID="3">
<CustomerAttributeValue>
<Value>3</Value>
</CustomerAttributeValue>
</CustomerAttribute>
<CustomerAttribute ID="4">
<CustomerAttributeValue>
<Value>waukegan</Value>
</CustomerAttributeValue>
</CustomerAttribute>
<CustomerAttribute ID="1">
<CustomerAttributeValue>
<Value>1</Value>
</CustomerAttributeValue>
</CustomerAttribute>
<CustomerAttribute ID="6">
<CustomerAttributeValue>
<Value>53045</Value>
</CustomerAttributeValue>
</CustomerAttribute>
<CustomerAttribute ID="7">
<CustomerAttributeValue>
<Value>John Smith</Value>
</CustomerAttributeValue>
</CustomerAttribute>
<CustomerAttribute ID="8">
<CustomerAttributeValue>
<Value>john.smith@mail.com
</CustomerAttributeValue>
</CustomerAttribute>
</Attributes>


We need to be able to loop through and parse out the XML data (from the SQL text field) so we can flatten out the data and maintain the relationship between the entity value and each of the separate attributes.

Something like this is our desired result to output to another table (based on sample XML data):
Entity,CustomerAttribute,CustomerAttributeValue
30,3,3
30,4,waukegan
30,1,1
30,6,53045
30,7,John Smith
30,8,john.smith@mail.com

The questions are :
What is the best way to approach extracting and parsing the XML data out of the SQL text field?
Is there a way to use the XML functions on data stored as text (and not XML)?

I think the easiest way is to dump content of the Field which contains XML into text files.
so for every record you will a file 1.xml,2.xml than load them into the database.
This tutorial provides an example
www.etl-tools.com/advanced-etl-processor...elds-into-files.html .
Than load data from xml files into the database table using mask.
You have very simple xml structure everything can be done within 30 minutes
More about working with XML
www.etl-tools.com/wiki/aetle:processing_data:reader:xml .

Please keep us posted on your progress

That approach makes sense - we'll give it a try.

Our hope was that there would be a way to transform it without going to an external file first since we already had the field in memory, but we'll take whatever works.

We'll post an update once we have tried the suggested route.

Questions:

How do you recommend we maintain our relationship with the original entity key (that is NOT in the XML data)?

We were thinking that perhaps we named the XML file with the entity value and then used that value from the file name when we processed the XML file and inserted into the database.

We need to maintain the relationship because parsed attributes and values all belong to the original entity (which comes from another SQL field).

Thoughts & suggestions on this aspect are appreciated.

>>>We were thinking that perhaps we named the XML file with the entity value and then used that value from the file name when we processed the XML file and inserted into the database.

Yes, this is how I would do it as well.

FYI: Use metadata transformation to get file name.
www.etl-tools.com/wiki/_media/aetle:tran...ation_properties.png