Dynamics Community Forum Thread Details

I have the following FetchXML statement and sql query.

The query is not returning the actual values for the filter conditions, such as the 100000012 from the it_semesteroperational condition node.

the query returns the exact number of rows I would expect, but the values are all null.

Any ideas?

Thanks,

Michael

DECLARE @xml XML

SET @xml = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="it_grtproduct">
            <attribute name="it_grtproductid"/>
            <attribute name="it_titleworkingtitle"/>
            <attribute name="it_1stauthor"/>
            <attribute name="it_devphase"/>
            <attribute name="it_amarc"/>
            <attribute name="it_pdctotalforecast"/>
            <attribute name="it_pdcannualtotal"/>
            <attribute name="it_pdcoctforecast"/>
            <attribute name="it_pdcnovforecast"/>
            <attribute name="it_pdcdecforecast"/>
            <attribute name="it_pdcmayforecast"/>
            <attribute name="it_pdcmarforecast"/>
            <attribute name="it_pdcjunforecast"/>
            <attribute name="it_pdcjulforecast"/>
            <attribute name="it_pdcjanforecast"/>
            <attribute name="it_pdcfebforecast"/>
            <attribute name="it_pdcaugforecast"/>
            <attribute name="it_pdcaprforecast"/>
            <attribute name="it_pdcsepforecast"/>
            <order attribute="it_amarc" descending="false"/>
            <filter attribute="FitlerName" type="and">
            <condition attribute="it_devphase" operator="in">
                  <value>100000000</value>
                  <value>100000001</value>
            </condition>
            <condition attribute="statecode" operator="eq" value="0"/>
            <condition attribute="it_semesteroperational" operator="in">
                        <value>100000012</value>
                        <value>100000014</value>
                        <value>100000013</value>
                        <value>100000015</value>
                        <value>100000002</value>
                        <value>100000005</value>
                        <value>100000008</value>
                        <value>100000011</value>
                        <value>100000000</value>
                        <value>100000003</value>
                        <value>100000006</value>
                        <value>100000009</value>
                        <value>100000001</value>
                        <value>100000004</value>
                        <value>100000007</value>
                        <value>100000010</value>
                        <value>100000017</value>
                        <value>100000018</value>
                        <value>100000016</value>
                        <value>100000025</value>
                        <value>100000019</value>
                        <value>100000024</value>
                        <value>100000020</value>
                        <value>100000021</value>
                  </condition>
                  <condition attribute="it_1stauthor" operator="not-null"/>
            </filter>
      </entity>
</fetch> '

SELECT cc.c.value('@attribute', 'varchar(100)') as [condition]
, v.d.value('@value', 'varchar(100)') as [Value]

FROM @xml.nodes('fetch') f(c)
      CROSS APPLY f.c.nodes('entity') e(c)
            CROSS APPLY e.c.nodes('filter') g(c)
            cross apply g.c.nodes('condition') cc(c)
            cross apply cc.c.nodes('value') v(d)

FetchXML conversion to sql query is missing some values

Helpful resources

News and Announcements

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Community AMA December 12th

Leaderboard

Featured topics

Product updates