Link to home
Start Free TrialLog in
Avatar of Jim Horn
Jim HornFlag for United States of America

asked on

Querying XML when node names are the same

Hi all

In the below XML I'm trying to extract the /CustomFields/Field/CustomFieldValue value of 'Inbound' WHERE  /CustomFields/Field/CustomFieldName = 'Inbound/Outbound'.  

The problem I'm having is with multiple <field> notes, I'm not handling the 'Get the /CustomFields/Field position where /CustomFields/Field/CustomFieldName = 'Inbound/Outbound', then return the /CustomFields/Field position of the above/CustomFieldValue value.   AND In the below example it would be [3], but it could be anywhere in other rows.

<CustomFields>
  <Field>
    <CustomFieldName>Product</CustomFieldName>
    <CustomFieldValue>Fuzzy Moose Slippers</CustomFieldValue>
  </Field>
  <Field>
    <CustomFieldName>Business Partner</CustomFieldName>
    <CustomFieldValue>Slippers R Us</CustomFieldValue>
  </Field>
  <Field>
    <CustomFieldName>Inbound/Outbound</CustomFieldName>    -- FIND THIS
    <CustomFieldValue>Inbound</CustomFieldValue>           -- GRAB THIS
  </Field>

Open in new window


Right now I have the below query.  The WHERE clause works, but it doesn't store the [?] position where it found it, so the SELECT needs to change so that [1] reflect wherever 'Inbound/Outbound' is.
SELECT TOP 100 
       a.snActivityId, 
       a.DateCreated, 
       a.CustomFields, 
       InboundOutbound = s.CustomFields.value('(/CustomFields/Field/CustomFieldValue)[1]', 'varchar(25)') 
FROM Activity a
WHERE s.CustomFields IS NOT NULL
       AND s.CustomFields.exist('/CustomFields/Field/CustomFieldValue[text() = "Inbound/Outbound"]') = 1

Open in new window


Thanks in advance.
Jim
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial