x

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

I have an XML field in a table in my database with data similar to :

<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
  <fields>
    <field name="::desc_of_operations::">
      <value />
    </field>
    <field name="::applicant_physical_address_2::">
      <value />
    </field>
    <field name="::agency_name::">
      <value>Test Insurance Services, Inc.</value>
    </field>
    <field name="::premium::">
      <value>1,234.56</value>
    </field>
    <field name="::subcosts_premium::">
      <value>94</value>
    </field>
  </fields>
  <f href="{href}" />
</xfdf>

and a query like:

WITH XMLNAMESPACES(DEFAULT 'http://ns.adobe.com/xfdf/')
SELECT  
    app_id, cast(cast(XFDF.query('data(//field[@name="::premium_total::"] [1])') 
    as varchar(100)) as money) premium_total,
    (XFDF.value('data(//field[@name="::premium_total::"][1])', 
        'numeric(18,0)'))  as  premium_total_1
    FROM Applications
    where 
        application_type_id in (49)
        and Inspection_Status = 2

With the .query and cast I can get what I need, but...

With the .value function I get the error: XQuery [Applications.xfdf.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

How Do I make my .value xquery a singleton?

more ▼

asked Jan 27, 2011 at 04:48 PM in Default

Row Z gravatar image

Row Z
33 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Jan 27, 2011 at 07:00 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Oleg, You the man! Worked perfect.
Jan 27, 2011 at 07:54 PM Row Z
Now that I have access to this data, it would make sense to index it, for using as a filters in queries. I was able to create a primary XML index, but correct me if I am wrong, but the primary XML index just indexes the XML structure. Can I create indexes a specific field or set of fields within the XML?
Jan 27, 2011 at 08:12 PM Row Z
@Row Z - yes you can - secondary XML indexes are what you want there. As Oleg's answer was the correct one (as ever), could you please mark the answer as correct by clicking the tick box to the left of his answer? Thanks.
Jan 28, 2011 at 03:08 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x151
x12

asked: Jan 27, 2011 at 04:48 PM

Seen: 6439 times

Last Updated: Jan 27, 2011 at 05:42 PM