Suggestions: xQuery where filter predicate delivers unexpected results

Question

I have the following xml which is being parsed via a MSSQL database using OPENXML with an xquery filter to grab the right rows. Unfortunately, it doesn't seem to grab the appropriate rows, which has me scratching my head.

Using the following XML, I only want to insert the single email address where the Method="Insert", and ignore the remaining two addresses where Method is not present or has another value (which were previously inserted).

<Entities xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ActiveEntityID="0">
   <Entity_Businesses>
      <Entity_Business EntityTypeID="5" EntityRoleTypeID="9" Method="Update" Name="test business 76" EIN="" EmployeeCount="75" TotalAssets="750000.00">
         <Entity_Emails>
            <Entity_Email ID="85" EmailAddress="jones@company.com" />
            <Entity_Email ID="0" EmailAddress="smith@company.com" Method="Insert"/>
         </Entity_Emails>
         <Entity_Contacts>
            <Entity_Contact ID="162" EntityTypeID="4" EntityRoleTypeID="9" FName="Joe" MName="k" LName="Smith" SSN="444-44-444" JobTitleID="0" DOB="2007-02-27T00:00:00">
               <Entity_Emails>
                  <Entity_Email ID="86" EmailAddress="individual@test.com"/>
               </Entity_Emails>
            </Entity_Contact>
         </Entity_Contacts>
      <Entity_Business>
   </Entity_Businesses>
</Entities>

I am using this sql statement:

INSERT into Entity_Email(bsCol, EmailAddress, xmlID, xmlPID)
SELECT DENSE_RANK() OVER( ORDER BY y.parentid ) AS elementid, z.EmailAddress, y.parentid, z.ID
FROM  OPENXML( @hDoc, '//Entity_Emails', 1 ) 
WITH (parentid int '@mp:parentid', id int '@mp:id' ) y
INNER JOIN OPENXML(@hDoc, N'//Entity_Emails/Entity_Email',1) WITH (EmailAddress nvarchar(100), xmlID int '@mp:id', parentid int '@mp:parentid') as z
ON y.id = z.parentid
WHERE @pRI.value('(//Entity_Emails/Entity_Email/@Method)[1]','nvarchar(50)') = 'Insert';

As-is, all three email addresses will be inserted, even though the first and last email node do not have a 'Method" attribute. However if I add 'Method = "DontAdd"' to the other two email addresses, nothing gets inserted.

I have also tried using the predicate:

WHERE @pRI.exist('//Entity_Emails/Entity_Email[@Method="Insert"]') =1;

The result is similar - it inserts all rows, and seems to ignore the fact that two of the Email_Address elements do not have an attribute Method="Insert", regardless of whether the Method attribute exists.

The goal is to filter the xml as it is shredded and only add the email address with the attribute Method="Insert". Right now what I believe I have is actually "If you find Method = 'Insert' in the dataset, insert all rows" vs. "if you find method = 'insert', insert only those rows which have that attribute."

Thank you in advance.

score 0 · Answer 1 · answered Feb 12 '17 at 18:56

Please note the following answer for those that might be helped in the future. After retrieving the column 'Method' in the z aliased query, I was able to use standard t-sql to filter the results correctly and then insert the correct rows.

INSERT into Entity_Email(bsCol, EmailAddress, xmlID, xmlPID)
    SELECT DENSE_RANK() OVER( ORDER BY y.parentid ) AS elementid, z.EmailAddress, z.xmlID, y.parentid
    FROM  OPENXML( @hDoc, '//Entity_Emails', 1 ) 
    WITH (parentid int '@mp:parentid', id int '@mp:id' ) y
    INNER JOIN OPENXML(@hDoc, N'//Entity_Emails/Entity_Email',1) WITH (EmailAddress nvarchar(100), xmlID int '@mp:id', parentid int '@mp:parentid', Method nvarchar(50) '@Method') as z
    ON y.id = z.parentid
    WHERE z.Method = 'Insert'

Suggestions: xQuery where filter predicate delivers unexpected results

1 Answers1