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;
User contributions licensed under CC BY-SA 3.0