postgresql : xml parsing : xpath return only one record, where is xml contains 3 records



I have a procedure in postgresql with xml type paramter. I need to parse xml and insert record in a table. I tried this way, but it is inserting only one record in table, where as xml contains three records.


the xml code is



Select (xpath('/ArrayOfUserGroupModulePermissionBusinessObject/UserGroupModulePermissionBusinessObject/ModuleID/text()', t1))[1]::varchar::BIGINT AS ModuleID
from (Select Perms::xml as t1) as t2;


the xml is here



<ArrayOfUserGroupModulePermissionBusinessObject xmlns:xsi="http://ift.tt/ra1lAU" xmlns:xsd="http://ift.tt/tphNwY">
<UserGroupModulePermissionBusinessObject>
<UserGroupModulePermissionID>0</UserGroupModulePermissionID>
<UserGroupID>0</UserGroupID>
<ModuleID>3</ModuleID>
<ModuleName>Administration</ModuleName>
<CanRead>false</CanRead>
<CanAdd>false</CanAdd>
<CanEdit>false</CanEdit>
<CanDelete>false</CanDelete>
<CanPrint>false</CanPrint>
</UserGroupModulePermissionBusinessObject>
<UserGroupModulePermissionBusinessObject>
<UserGroupModulePermissionID>0</UserGroupModulePermissionID>
<UserGroupID>0</UserGroupID>
<ModuleID>2</ModuleID>
<ModuleName>Data Management</ModuleName>
<CanRead>false</CanRead>
<CanAdd>false</CanAdd>
<CanEdit>false</CanEdit>
<CanDelete>false</CanDelete>
<CanPrint>false</CanPrint>
</UserGroupModulePermissionBusinessObject>
<UserGroupModulePermissionBusinessObject>
<UserGroupModulePermissionID>0</UserGroupModulePermissionID>
<UserGroupID>0</UserGroupID>
<ModuleID>1</ModuleID>
<ModuleName>Reception</ModuleName>
<CanRead>false</CanRead>
<CanAdd>false</CanAdd>
<CanEdit>false</CanEdit>
<CanDelete>false</CanDelete>
<CanPrint>false</CanPrint>
</UserGroupModulePermissionBusinessObject>
</ArrayOfUserGroupModulePermissionBusinessObject>


I don't know what is wrong in this sql


No comments:

Post a Comment