How can I parse XML from database field?

Question

Working with the following XML.

<TEMPLATE>
<TEMPLATE_FIELD prompt="TEST_ONE" data_type="8" tag="TEST_ONE" required="0" is_radio="0" default_value="" SQL="SELECT * FROM DBO.SOME_NEW_TEST" />
<TEMPLATE_FIELD prompt="TEST_TWO" data_type="8" tag="TEST_TWO" required="0" is_radio="0" default_value="" SQL="SELECT * FROM DBO.SOME_NEW_TEST" />
<TEMPLATE_FIELD prompt="TEST_THREE" data_type="8" tag="TEST_THREE" required="0" is_radio="0" default_value="" SQL="SELECT * FROM DBO.SOME_NEW_TEST" />

I'm trying to obtain the following - or something similar in which I can read the fields efficiently.

I've tried following several other questions and found the 3rd answer of this question promising https://stackoverflow.com/a/15349737/8715626 --because I want to be able to parse in SQL and not using an external language or creating a file. If creating a file is easier, please feel free to share. I've tried several of variations of the code below and I either get a "blank" or a NULL.

declare @xml as xml
set @xml = (SELECT xml_field from fooXMLtable)

select @xml

;with cte as (    
  select @xml xmlstring
)
SELECT 
xmlstring.value('(/TEMPLATE//TEMPLATE_FIELD/prompt/node())[3]','VARCHAR(max)')
as prompt

Answer 1

If you're using MSSQL, this should work:

DECLARE @xml XML = '<TEMPLATE>
<TEMPLATE_FIELD prompt="TEST_ONE" data_type="8" tag="TEST_ONE" required="0" is_radio="0" default_value="" SQL="SELECT * FROM DBO.SOME_NEW_TEST" />
<TEMPLATE_FIELD prompt="TEST_TWO" data_type="8" tag="TEST_TWO" required="0" is_radio="0" default_value="" SQL="SELECT * FROM DBO.SOME_NEW_TEST" />
<TEMPLATE_FIELD prompt="TEST_THREE" data_type="8" tag="TEST_THREE" required="0" is_radio="0" default_value="" SQL="SELECT * FROM DBO.SOME_NEW_TEST" />
</TEMPLATE>'

SELECT 
    T.C.value('./@prompt', 'nvarchar(50)') as Prompt,
    T.C.value('./@data_type', 'int') as DataType,
    T.C.value('./@tag', 'nvarchar(50)') as Tag,
    T.C.value('./@required', 'bit') as Required,
    T.C.value('./@is_radio', 'bit') as IsRadio,
    T.C.value('./@default_value', 'nvarchar(max)') as DefaultValue,
    T.C.value('./@SQL', 'nvarchar(max)') as Query
FROM   @xml.nodes('/TEMPLATE/TEMPLATE_FIELD') T(C)