How to count values in XML structure using XQuery statement
-
Here is what my XML document looks like.
<DATAMATRIX>
<FSDATAMATRIX>
<DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE>
<FIELD1>BELGIUM</FIELD1>
<FIELD2>BEVERAGES, NON-ALCOHOLIC</FIELD2>
<FIELD4>NO</FIELD4>
<FIELD5>YES</FIELD5>
</FSDATAMATRIX>
<FSDATAMATRIX>
<DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE>
<FIELD1>BRAZIL</FIELD1>
<FIELD2>CHEESE</FIELD2>
<FIELD4>NO</FIELD4>
<FIELD5>YES</FIELD5>
</FSDATAMATRIX>
</DATAMATRIX>What I need to do it perform a count on the above XML document to see if an entry for "BELGIUM" already exists. If it doesn't exist, I will perform an insert, otherwise I will update the values. How do I perform a count on FIELD1 where it matches a variable ? Something like: count FIELD1=sql:variable("@XCOUNTRY") Thanks again, my data loading project is nearly complete. This should be the last step.
-
Here is what my XML document looks like.
<DATAMATRIX>
<FSDATAMATRIX>
<DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE>
<FIELD1>BELGIUM</FIELD1>
<FIELD2>BEVERAGES, NON-ALCOHOLIC</FIELD2>
<FIELD4>NO</FIELD4>
<FIELD5>YES</FIELD5>
</FSDATAMATRIX>
<FSDATAMATRIX>
<DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE>
<FIELD1>BRAZIL</FIELD1>
<FIELD2>CHEESE</FIELD2>
<FIELD4>NO</FIELD4>
<FIELD5>YES</FIELD5>
</FSDATAMATRIX>
</DATAMATRIX>What I need to do it perform a count on the above XML document to see if an entry for "BELGIUM" already exists. If it doesn't exist, I will perform an insert, otherwise I will update the values. How do I perform a count on FIELD1 where it matches a variable ? Something like: count FIELD1=sql:variable("@XCOUNTRY") Thanks again, my data loading project is nearly complete. This should be the last step.
-
SELECT @XMLDOC.value('count(/DATAMATRIX/FSDATAMATRIX[FIELD1=sql:variable("@XCOUNTRY")])','int')
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
Perfect. My data load project is now complete. I could not have done it without your help. Thanks again. David
-
Perfect. My data load project is now complete. I could not have done it without your help. Thanks again. David