Databases
StackOverflow2013
Postsdbo
POSQL Server 2008 R2 SQLXML and xpath query filter not working
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server 2008 R2 SQLXML and xpath query filter not working
    text
    copied!<p>I have the following schema returning four projects when running sql xml:</p> <pre><code>&lt;xsd:element name="iati-activities" sql:relation="IATI.ACTIVITIES" sql:key-fields="BUSINESS_UNIT"&gt; &lt;xsd:complexType&gt; &lt;xsd:sequence&gt; &lt;xsd:element name="iati-activity" sql:relation="IATI.PROJECT_METADATA" sql:relationship="ACTIVITIES_ACTIVITY"&gt; &lt;xsd:complexType&gt; &lt;xsd:sequence&gt; &lt;xsd:element name="ProjectId" type="xsd:string" sql:field="PROJECT_ID" sql:key-fields="PROJECT_ID" /&gt; &lt;/xsd:sequence&gt; &lt;/xsd:complexType&gt; &lt;/xsd:element&gt; &lt;/xsd:sequence&gt; &lt;xsd:complexType&gt; &lt;/xsd:element&gt; </code></pre> <p>I want to get:</p> <pre><code>&lt;iati-activities&gt; &lt;iati-activity&gt; &lt;ProjectId&gt;00072877&lt;/ProjectId&gt; &lt;/iati-activity&gt; &lt;/iati-activities&gt; </code></pre> <p>But when running it does not filter. Here is the xpath-query I use:</p> <pre><code>&lt;root xmlns:sql="urn:schemas-microsoft-com:xml-sql"&gt; &lt;sql:header sql:nullvalue="ISNULL"&gt; &lt;sql:param name="ProjectId"&gt;00072877&lt;/sql:param&gt; &lt;/sql:header&gt; &lt;sql:xpath-query mapping-schema="activity_schema.xsd"&gt; iati-activities[iati-activity/ProjectId=$ProjectId] &lt;/sql:xpath-query&gt; &lt;/root&gt; </code></pre> <p>This is what I get:</p> <pre><code> &lt;iati-activities&gt; &lt;iati-activity&gt; &lt;ProjectId&gt;00072877&lt;/ProjectId&gt; &lt;/iati-activity&gt; &lt;iati-activity&gt; &lt;ProjectId&gt;00059626&lt;/ProjectId&gt; &lt;/iati-activity&gt; &lt;iati-activity&gt; &lt;ProjectId&gt;...&lt;/ProjectId&gt; &lt;/iati-activity&gt; &lt;iati-activity&gt; &lt;ProjectId&gt;...&lt;/ProjectId&gt; &lt;/iati-activity&gt; &lt;/iati-activities&gt; </code></pre> <p>If I use the following query:</p> <pre><code>&lt;root xmlns:sql="urn:schemas-microsoft-com:xml-sql"&gt; &lt;sql:header sql:nullvalue="ISNULL"&gt; &lt;sql:param name="ProjectId"&gt;00072877&lt;/sql:param&gt; &lt;/sql:header&gt; &lt;sql:xpath-query mapping-schema="activity_schema.xsd"&gt; iati-activities/iati-activity[ProjectId=$ProjectId] &lt;/sql:xpath-query&gt; &lt;/root&gt; </code></pre> <p>I get the below without the iati-acitivties element:</p> <pre><code>&lt;iati-activity&gt; &lt;ProjectId&gt;00072877&lt;/ProjectId&gt; &lt;/iati-activity&gt; </code></pre> <p>Any one know what I'm doing wrong?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload