How Do I make my .value xquery a singleton? 'value()' requires a singleton error using .value in an XML Query

The attributes in your xml and the script don't match, but here is the script which will work:

with xmlnamespaces(default 'http://ns.adobe.com/xfdf/')
select
    app_id,
    XFDF.value('(//field[@name="::agency_name::"]/value)[1]', 
        'varchar(50)') agency_name,  
    XFDF.value('(//field[@name="::premium::"]/value)[1]', 
        'money') premium_total,
    XFDF.value('(//field[@name="::subcosts_premium::"]/value)[1]', 
        'numeric(18, 0)') subcosts_premium
    from Applications
    where 
        application_type_id in (49)
        and Inspection_Status = 2;

--The above query will return something like this:

app_id      premium_total                    premium_total     subcosts_premium
----------- -------------------------------- ----------------- ----------------
7           Test Insurance Services, Inc.    1234.56           94

The idea is that because you have xml shape such that you only need it to return a single value for each .value() applied, you simply need to point it to the fragment which is uniquely identified. In this case, though the field nodes make up a collection, they all have unique attribute named name. Therefore, if you pass something like
//field[@name="desired_name"]/value as the exact location where the inner text is located, adding [1] to it will do the trick (to select InnerText). The only important thing to remember is that the //field[@name="desired_name"]/value needs to be enclosed in parenthesis, making it look like this:

(//field[@name="desired_name"]/value)[1] as the first parameter to the value() method. The second parameter is the name of the data type.

Hope this helps,

Oleg