Problem with parsing XML with multiple nodes into SQL

0

I got problem with node called "siBikNetQuery" and next 3 inside 'inquiryHeader'. There is a problem :

"The XML parse error 0xc00ce553 occurred on line number 0, near the XML text "". Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1 [Batch Start Line 1] The error description is 'The following tags were not closed: SiBikNet, siBikNetQuery, creditInquiry, inquiryHeader, testDataMarker.'. Msg 8179, Level 16, State 5, Line 42 Could not find prepared statement with handle 0. Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1 [Batch Start Line 1] sp_xml_removedocument: The value supplied for parameter number 1 is invalid."

I have no idea what's wrong here.

 DECLARE @Handle AS INT; -- The handle of the XML data, passed to sp_xml_preparedocument
    DECLARE @Xml AS NVARCHAR(1000); -- The XML document for this example


    SET @Xml = N'
    <SiBikNet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://www.ws.bik.pl/ws/ki/2v2/types">
     <BIK_REQUEST xmlns="">
            <consentDate>2018-08-04</consentDate>
            <citizenshipStatus>citizen</citizenshipStatus>
            <nationality>PL</nationality>
            <pesel>123</pesel>
            <documentType>idCard</documentType>
            <documentId>CCH12</documentId>
            <surname>xxx</surname>
            <forename>yyy</forename>
            <country>PL</country>
            <postcode>11111</postcode>
            <city>wqreewqw</city>
            <street>wetww</street>
            <houseNumber>23</houseNumber>
            <localNumber>32</localNumber>
            <numberOfParticipants>1</numberOfParticipants>
            <applicationCurrency>PLN</applicationCurrency>
            <creditAmount>2000</creditAmount>
            <clientRelationToApplication>mainBorrower</clientRelationToApplication>
             </BIK_REQUEST>
     <siBikNetQuery xmlns="">
          <creditInquiry>
            <inquiryHeader>      
              <subscriberId>57000002</subscriberId>
              <subscriberUnitId>57000002</subscriberUnitId>
              <testDataMarker>false</testDataMarker>
            </inquiryHeader>
           </creditInquiry>
       </siBikNetQuery>
    </SiBikNet>';


    EXEC sys.sp_xml_preparedocument @Handle OUTPUT , @Xml, N'<SiBikNet xmlns:t="https://www.ws.bik.pl/ws/ki/2v2/types"/>'; --Prepare a parsed document 

    SELECT *
    FROM
           OPENXML(@Handle,'/*[local-name()="SiBikNet"]', 2)
               WITH (   consentDate NVARCHAR(10) '/*[local-name()="BIK_REQUEST"]',
                        citizenshipStatus NVARCHAR(10) '/*[local-name()="BIK_REQUEST"]',
                        nationality  NVARCHAR(10) '/*[local-name()="BIK_REQUEST"]' ,
                        subscriberId NVARCHAR(10) '/*[local-name()="siBikNetQuery"]',
                        subscriberUnitId NVARCHAR(10) '/*[local-name()="siBikNetQuery"]',
                        testDataMarker  NVARCHAR(10) '/*[local-name()="siBikNetQuery"]'
                    );

    EXEC sys.sp_xml_removedocument @Handle; 

Another problem is that when I delete these 3 rows from XML path then error disappeares but I got NULL values (there is problem with path on particular attributes I believe):

 DECLARE @Handle AS INT; -- The handle of the XML data, passed to sp_xml_preparedocument
DECLARE @Xml AS NVARCHAR(1000); -- The XML document for this example


SET @Xml = N'
<SiBikNet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://www.ws.bik.pl/ws/ki/2v2/types">
 <BIK_REQUEST xmlns="">
        <consentDate>2018-08-04</consentDate>
        <citizenshipStatus>citizen</citizenshipStatus>
        <nationality>PL</nationality>
        <pesel>123</pesel>
        <documentType>idCard</documentType>
        <documentId>CCH12</documentId>
        <surname>xxx</surname>
        <forename>yyy</forename>
        <country>PL</country>
        <postcode>11111</postcode>
        <city>wqreewqw</city>
        <street>wetww</street>
        <houseNumber>23</houseNumber>
        <localNumber>32</localNumber>
        <numberOfParticipants>1</numberOfParticipants>
        <applicationCurrency>PLN</applicationCurrency>
        <creditAmount>2000</creditAmount>
        <clientRelationToApplication>mainBorrower</clientRelationToApplication>
         </BIK_REQUEST>
 <siBikNetQuery xmlns="">
      <creditInquiry>
        <inquiryHeader>      



        </inquiryHeader>
       </creditInquiry>
   </siBikNetQuery>
</SiBikNet>';


EXEC sys.sp_xml_preparedocument @Handle OUTPUT , @Xml, N'<SiBikNet xmlns:t="https://www.ws.bik.pl/ws/ki/2v2/types"/>'; --Prepare a parsed document 

SELECT *
FROM
       OPENXML(@Handle,'/*[local-name()="SiBikNet"]', 2)
           WITH (   consentDate NVARCHAR(10) '/*[local-name()="BIK_REQUEST"]',
                    citizenshipStatus NVARCHAR(10) '/*[local-name()="BIK_REQUEST"]',
                    nationality  NVARCHAR(10) '/*[local-name()="BIK_REQUEST"]' ,
                    subscriberId NVARCHAR(10) '/*[local-name()="siBikNetQuery"]',
                    subscriberUnitId NVARCHAR(10) '/*[local-name()="siBikNetQuery"]',
                    testDataMarker  NVARCHAR(10) '/*[local-name()="siBikNetQuery"]'
                );

EXEC sys.sp_xml_removedocument @Handle; 
sql
xml
openxml
asked on Stack Overflow Oct 16, 2018 by Sebastian Kozik

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0