phil-factor/jsonsqlserverroutines

Various stored procedures that are used to import and export JSON Data

jsonsqlserverroutines's Issues

TablesFromJson failing with (valid) complex json structure

@Phil-Factor

Msg 13607, Level 16, State 4, Line 41
JSON path is not properly formatted. Unexpected character '0' is found at position 2.

[ { "integrationGUID": "f347433e-141d-4428-b5a8-8b103f815629", "tenantId": "Company1", "createdAt": "0001-01-01T00:00:00+00:00", "updatedAt": "0001-01-01T00:00:00+00:00", "isActive": true, "name": "Joe person", "emaNY": "[email protected]", "phone": "[phone]", "commuteTimeInMin": 20, "wipLimit": 5, "claimCapacity": 5, "efficiency": 5, "companies": [ { "externalResourceId": "00056", "integrationGUID": "a4bb759b-174b-4e14-a9f2-4ed7fdd069ab" } ], "offices": [ { "integrationGUID": "39625855-41f4-491f-8779-64a99d55103b" } ], "teams": [ { "integrationGUID": "a7d049f1-066c-44c1-bfbb-c6a9ab1118ec" } ], "roles": [ { "integrationGUID": "c60a2316-7289-412d-bfe3-65cb2155bf02" } ], "locations": [ { "integrationGUID": "51383818-9d6e-49bb-840d-4bc47b53d633", "name": "Office", "street": "601 Maple Ln", "city": "Wheeling", "state": "NY", "zip": "11111", "unit": "", "lat": 0.0, "long": 0.0, "startDate": "2022-01-01T08:00:00+00:00", "endDate": "2023-01-01T08:00:00+00:00", "allowedTravelRadius": 50, "monday": true, "tuesday": true, "wednesday": true, "thursday": true, "friday": true, "saturday": false, "sunday": false }, { "integrationGUID": "293bc272-b752-4b88-86f7-4c45696bfe5f", "name": "InActive Office", "street": "601 Maple Ln", "city": "Wheeling", "state": "NY", "zip": "11111", "unit": "", "lat": 0.0, "long": 0.0, "startDate": "2022-01-01T08:00:00+00:00", "endDate": "2023-01-01T08:00:00+00:00", "allowedTravelRadius": 50, "monday": true, "tuesday": true, "wednesday": true, "thursday": true, "friday": true, "saturday": false, "sunday": false } ], "selectedAttributeDefinitions": [ { "integrationGUID": "c60a2316-7289-412d-bfe3-65cb2155bf02", "displayName": "Complex Loss", "attributeDefinitionIntegrationGUID": "db266fc6-f4be-4faa-81a6-dddf6fb8d007", "selectedAttributeValuesVarchars": [ { "selectedAttributeDefinitionIntegrationGUID": "62d9dae1-0753-482c-aae9-a36934fcd313", "displayName": "Interior Estimating", "attributeDefinitionValueIntegrationGUID": "faff4b46-8545-468d-96aa-2389d0c705ed", "integrationGUID": "4c755f61-8dd8-4198-ba9d-d4c59c421bb5", "value": "Interior Estimating" }, { "selectedAttributeDefinitionIntegrationGUID": "62d9dae1-0753-482c-aae9-a36934fcd313", "displayName": "Exterior Estimating", "attributeDefinitionValueIntegrationGUID": "be7390fe-9ab4-4570-bc79-7fbed0a3b4ac", "integrationGUID": "0af49047-1438-4c8a-9f0d-d84352a95956", "value": "Exterior Estimating" }, { "selectedAttributeDefinitionIntegrationGUID": "62d9dae1-0753-482c-aae9-a36934fcd313", "displayName": "Contents", "attributeDefinitionValueIntegrationGUID": "e44da5e8-24df-467e-8c72-91a2e225ca65", "integrationGUID": "1aa573d3-1747-42ee-8add-448a86347a70", "value": "Contents" } ], "selectedAttributeValuesInts": [], "selectedAttributeValuesFloats": [], "selectedAttributeValuesBools": [] }, { "integrationGUID": "e7586e79-63d1-41e3-bfb7-a90c230bf5fc", "displayName": "PerNYs_", "attributeDefinitionIntegrationGUID": "66fa6b81-6080-405c-aabe-51cf00ef406e", "selectedAttributeValuesVarchars": [ { "selectedAttributeDefinitionIntegrationGUID": "e7586e79-63d1-41e3-bfb7-a90c230bf5fc", "displayName": "Fire-Lightening", "attributeDefinitionValueIntegrationGUID": "7c915305-85f8-44e4-90c6-de724c772999", "integrationGUID": "dcead5c0-b6d5-4255-80ed-f21f04cfd086", "value": "Fire-Lightening" }, { "selectedAttributeDefinitionIntegrationGUID": "e7586e79-63d1-41e3-bfb7-a90c230bf5fc", "displayName": "Water", "attributeDefinitionValueIntegrationGUID": "7fe5ea4d-b227-4cb9-b12e-d51678c1cf69", "integrationGUID": "f41f753e-65ef-43dd-97f6-2aaba4b16cd2", "value": "Water" }, { "selectedAttributeDefinitionIntegrationGUID": "e7586e79-63d1-41e3-bfb7-a90c230bf5fc", "displayName": "Wind-HaNY", "attributeDefinitionValueIntegrationGUID": "9259a0a3-c323-49f2-8b0b-ee28bcda5aa2", "integrationGUID": "90c1abb8-9bb6-4b64-9813-3672deede744", "value": "Wind-HaNY" }, { "selectedAttributeDefinitionIntegrationGUID": "e7586e79-63d1-41e3-bfb7-a90c230bf5fc", "displayName": "Smoke", "attributeDefinitionValueIntegrationGUID": "342000f4-e367-445a-974c-52820e331e65", "integrationGUID": "7b6b3dd4-b4f2-4884-9ecf-045ef407003b", "value": "Smoke" } ], "selectedAttributeValuesInts": [], "selectedAttributeValuesFloats": [], "selectedAttributeValuesBools": [] }, { "integrationGUID": "7e42a850-c50b-42ac-9962-679b0645a088", "displayName": "SkNYls_", "attributeDefinitionIntegrationGUID": "cd92d507-082f-40cc-a397-5f285058b530", "selectedAttributeValuesVarchars": [ { "selectedAttributeDefinitionIntegrationGUID": "7e42a850-c50b-42ac-9962-679b0645a088", "displayName": "Xactimate", "attributeDefinitionValueIntegrationGUID": "bc952a30-963c-4883-b59f-cfb1fdaf0ce3", "integrationGUID": "c101dce3-89a7-4351-9c08-bdb6e4e0730c", "value": "Xactimate" }, { "selectedAttributeDefinitionIntegrationGUID": "7e42a850-c50b-42ac-9962-679b0645a088", "displayName": "MobNYe Claims", "attributeDefinitionValueIntegrationGUID": "44304c89-1315-4417-bdae-e2272db4e55e", "integrationGUID": "aadda04a-ba3e-485b-98ac-5eb0f9534542", "value": "MobNYe Claims" }, { "selectedAttributeDefinitionIntegrationGUID": "7e42a850-c50b-42ac-9962-679b0645a088", "displayName": "Virtual Assist", "attributeDefinitionValueIntegrationGUID": "b3e80dab-a4ec-45cb-9cc7-48a1c5986408", "integrationGUID": "9de364da-38e8-49aa-b5d6-f9fcdb1ce1fc", "value": "Virtual Assist" } ], "selectedAttributeValuesInts": [], "selectedAttributeValuesFloats": [], "selectedAttributeValuesBools": [] }, { "integrationGUID": "97e7df6e-ab20-4c57-98ef-9f8a2e85ca4b", "displayName": "Roof Assessment", "attributeDefinitionIntegrationGUID": "bf27e6aa-edde-4cbf-a2ae-0b6d471b060c", "selectedAttributeValuesVarchars": [], "selectedAttributeValuesInts": [], "selectedAttributeValuesFloats": [], "selectedAttributeValuesBools": [ { "selectedAttributeDefinitionIntegrationGUID": "97e7df6e-ab20-4c57-98ef-9f8a2e85ca4b", "displayName": "DisplayName", "attributeDefinitionValueIntegrationGUID": "9e8917c6-297c-496f-b0a0-f6015aeb4cdd", "integrationGUID": "2f76960a-51ef-4357-b155-9f54d6611dd0", "value": true } ] }, { "integrationGUID": "66b9fbba-3cde-4465-a987-a0439cac8176", "displayName": "Languages", "attributeDefinitionIntegrationGUID": "2964c229-9ed8-49da-8675-a7c4edeffda2", "selectedAttributeValuesVarchars": [ { "selectedAttributeDefinitionIntegrationGUID": "66b9fbba-3cde-4465-a987-a0439cac8176", "displayName": "English", "attributeDefinitionValueIntegrationGUID": "5ac2f1c4-55e8-4ff1-98d5-29980a597932", "integrationGUID": "5cd9356f-9c5b-4509-8145-568aac14b8b8", "value": "English" } ], "selectedAttributeValuesInts": [], "selectedAttributeValuesFloats": [], "selectedAttributeValuesBools": [] }, { "integrationGUID": "c8b9a59d-cd32-46a2-852c-ffbb6c5ae1b6", "displayName": "Outside Adjuster_", "attributeDefinitionIntegrationGUID": "d892f277-020e-4525-b7e7-e3ad464449d8", "selectedAttributeValuesVarchars": [], "selectedAttributeValuesInts": [], "selectedAttributeValuesFloats": [], "selectedAttributeValuesBools": [ { "selectedAttributeDefinitionIntegrationGUID": "c8b9a59d-cd32-46a2-852c-ffbb6c5ae1b6", "displayName": "DisplayName", "attributeDefinitionValueIntegrationGUID": "573a1205-67b3-465f-a4a0-39c6f5a05e9f", "integrationGUID": "7e33f265-c17e-454b-a057-067692361663", "value": true } ] } ], "selectedCertificationsAndLicenses": [ { "integrationGUID": "1d848af2-e99f-4a91-85e7-aaedbdb96464", "certificationAndLicenseIntegrationGUID": "00ab0a30-5c08-40bd-99ae-dbf950e9d32f", "licenseNumber": "TL-1234-12-A-9933", "verificationStatus": "active", "documentCount": 0, "issueDate": "2022-04-08T00:00:00+00:00", "expirationDate": "2023-04-08T00:00:00+00:00" } ] } ]