Hi,

You can use the xml methods to shred the xml to a flat table but because the data is stored in a text column, this will need to be casted to xml first. See example query that shreds the xml to a flat table. I've used a CTE but you could do it other ways depending on your needs and environment. I've used the .nodes method to shred the xml and the .value method to extract the specific data:

WITH xCTE AS

(

SELECT f_int_id ,

e_int_id ,

f_type_in_id ,

CAST(form_xml AS XML) AS form_xml

FROM dbo.ABC_forms

)

select t.c.value('local-name(.)', 'varchar(100)') as 'NodeName',

t.c.value('@name', 'varchar(100)') as 'Name',

t.c.value('@index', 'int') as 'Index',

t.c.value('@setName', 'varchar(100)') as 'setName',

t.c.value('@type', 'varchar(100)') as 'Type',

t.c.value('(value/text())[1]', 'varchar(100)') as 'NodeValue'

FROM xCTE

CROSS APPLY form_xml.nodes('(/form/metadata/prop, /form/field)') t(c)