Online Tutorials & Training Materials | STechies.com
Register Login

SQL statement does not return row

|| || 2

SQL statement does not return row
Stechies

How to use OpenXML function and return the valueField values from the XML file?

Hello SAP Experts,

The below SQL statement do not return any row:

select valueField from openXML ( xp_read_file( 'C:SQLAnyEquipment.xml' ),
'/EquipmentType/descriptionField/DescriptionType' )
with ( valueField VARCHAR(80) 'valueField',
languageIDField varchar(20) './@languageIDField'

);

Please follow the steps below in order to re-generate the issue above:

  • Following is the structure of Equipment.xml file

<EquipmentType xmlns:i="www.w3.org/2001/XMLSchema-instance" xmlns="schemas.datacontract.org/2004/07/B2MML.NET">
<descriptionField>
<DescriptionType>
<languageIDField i:nil="true" />
<valueField>Lesen (alle) GET: api/Equipment/</valueField>
</DescriptionType>
</descriptionField>
<equipmentAssetMappingField i:nil="true" />
<equipmentCapabilityTestSpecificationIDField i:nil="true" />
<equipmentClassIDField i:nil="true" />
<equipmentField>
<EquipmentType>
<descriptionField>
<DescriptionType>
<languageIDField i:nil="true" />
<valueField>Kneter 002, 2.000 L, Knetraum</valueField>
</DescriptionType>
</descriptionField>
<equipmentAssetMappingField i:nil="true" />
<equipmentCapabilityTestSpecificationIDField i:nil="true" />
<equipmentClassIDField>
<EquipmentClassIDType>
<schemeAgencyIDField i:nil="true" />
<schemeAgencyNameField i:nil="true" />
<schemeDataURIField i:nil="true" />
<schemeIDField>PI01</schemeIDField>
<schemeNameField i:nil="true" />
<schemeURIField i:nil="true" />
<schemeVersionIDField i:nil="true" />
<valueField i:nil="true" />
</EquipmentClassIDType>
</equipmentClassIDField>
<equipmentField i:nil="true" />
<equipmentLevelField i:nil="true" />
<equipmentPropertyField i:nil="true" />
<hierarchyScopeField i:nil="true" />
<idField>
<schemeAgencyIDField i:nil="true" />
<schemeAgencyNameField i:nil="true" />
<schemeDataURIField i:nil="true" />
<schemeIDField>0002 </schemeIDField>
<schemeNameField i:nil="true" />
<schemeURIField i:nil="true" />
<schemeVersionIDField i:nil="true" />
<valueField i:nil="true" />
</idField>
<locationField i:nil="true" />
</EquipmentType>
<EquipmentType>
<descriptionField>
<DescriptionType>
<languageIDField i:nil="true" />
<valueField>HT-WEP-Verpackungen</valueField>
</DescriptionType>
</descriptionField>
<equipmentAssetMappingField i:nil="true" />
<equipmentCapabilityTestSpecificationIDField i:nil="true" />
<equipmentClassIDField>
<EquipmentClassIDType>
<schemeAgencyIDField i:nil="true" />
<schemeAgencyNameField i:nil="true" />
<schemeDataURIField i:nil="true" />
<schemeIDField>PI01</schemeIDField>
<schemeNameField i:nil="true" />
<schemeURIField i:nil="true" />
<schemeVersionIDField i:nil="true" />
<valueField i:nil="true" />
</EquipmentClassIDType>
</equipmentClassIDField>
<equipmentField i:nil="true" />
<equipmentLevelField i:nil="true" />
<equipmentPropertyField i:nil="true" />
<hierarchyScopeField i:nil="true" />
<idField>
<schemeAgencyIDField i:nil="true" />
<schemeAgencyNameField i:nil="true" />
<schemeDataURIField i:nil="true" />
<schemeIDField>WEP-V40 </schemeIDField>
<schemeNameField i:nil="true" />
<schemeURIField i:nil="true" />
<schemeVersionIDField i:nil="true" />
<valueField i:nil="true" />
</idField>
<locationField i:nil="true" />
</EquipmentType>
</equipmentField>
<equipmentLevelField i:nil="true" />
<equipmentPropertyField i:nil="true" />
<hierarchyScopeField i:nil="true" />
<idField>
<schemeAgencyIDField i:nil="true" />
<schemeAgencyNameField>Henkel AG &amp; Co. KGaA</schemeAgencyNameField>
<schemeDataURIField i:nil="true" />
<schemeIDField i:nil="true" />
<schemeNameField>EquipmentType</schemeNameField>
<schemeURIField>http://heidelberg:83/api/Equipment/</schemeURIField>
<schemeVersionIDField>0600</schemeVersionIDField>
<valueField i:nil="true" />
</idField>
<locationField i:nil="true" />
</EquipmentType>

  • Now please start the database server and InteractiveSQL and connect this server to the database file and execute the query given below:

select valueField from openXML ( xp_read_file( 'C:SQLAnyEquipment.xml' ),
'/EquipmentType/descriptionField/DescriptionType' )
with ( valueField VARCHAR(80) 'valueField',
languageIDField varchar(20) './@languageIDField'

);

  • You will notice that no row is returned in the result set.

Please help me to resolve this issue.


Comments

  • 22 Aug 2017 4:49 pm

    In order to resolve this issue execute the following code in InteractiveSQL and then:

    create or replace variable @rawxml xml;

    set @rawxml=cast(xp_read_file('C:SQLAnyEquipment.xml') as XML);

    select * from openxml(@rawxml,'//*:DescriptionType') with ( valueField VARCHAR(80) '*:valueField', languageIDField varchar(20) '*:languageIDField');

  • 22 Aug 2017 4:50 pm

    The above issue is caused due to incorrect syntax.

Related Articles

0.0117 seconds.