While I'm poking away at this, I thought I'd check with the collective wisdom. I've found a situation where I want to take advantage of CURSORTOXML and XMLTOCURSOR. The issue I'm running into is if the cursor being converted to XML has fields with NULL values in the first record, this appears to affect the resulting XMLTOCURSOR conversion. Any NULL columns will be missing from the result cursor even if later rows in the XML contain values. The hacky workaround seems to be I have to make sure that the first record has non-null values for all columns I want in the cursor. Anyone have any other magic tricks?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
I tend to create the cursor and use flags to append to the existing cursor.
Tracy
On March 29, 2022 7:01:47 PM EDT, Richard Kaye rkaye@invaluable.com wrote:
While I'm poking away at this, I thought I'd check with the collective wisdom. I've found a situation where I want to take advantage of CURSORTOXML and XMLTOCURSOR. The issue I'm running into is if the cursor being converted to XML has fields with NULL values in the first record, this appears to affect the resulting XMLTOCURSOR conversion. Any NULL columns will be missing from the result cursor even if later rows in the XML contain values. The hacky workaround seems to be I have to make sure that the first record has non-null values for all columns I want in the cursor. Anyone have any other magic tricks?
TIA
--
rk
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
VGhhdOKAmXMgYW5vdGhlciBwb3NzaWJpbGl0eS4gVGhhbmtzLCBUcmFjeS4g8J+Yig0KDQpUaGUgb3JpZ2luYWwgZGF0YSBzb3VyY2UgaXMgY29taW5nIGZyb20gYSBTUUwgYmFja2VuZCBzbyBJ4oCZdmUganVzdCBhZGRlZCBzb21lIElTTlVMTCBjYWxscyBhcm91bmQgdGhlIGZpZWxkcyB3aGljaCBtYXR0ZXIgdG8gdGhlIENVUlNPUlRPWE1ML1hNTFRPQ1VSU09SIGNhbGxzLg0KDQotLQ0KDQpyaw0KDQpGcm9tOiBQcm9mb3hUZWNoIDxwcm9mb3h0ZWNoLWJvdW5jZXNAbGVhZmUuY29tPiBPbiBCZWhhbGYgT2YgVHJhY3kgUGVhcnNvbg0KU2VudDogVHVlc2RheSwgTWFyY2ggMjksIDIwMjIgNzozMyBQTQ0KVG86IHByb2ZveHRlY2hAbGVhZmUuY29tDQpTdWJqZWN0OiBSZTogQ1VSU09SVE9YTUwgYW5kIE5VTExzDQoNCkkgdGVuZCB0byBjcmVhdGUgdGhlIGN1cnNvciBhbmQgdXNlIGZsYWdzIHRvIGFwcGVuZCB0byB0aGUgZXhpc3RpbmcgY3Vyc29yLg0KDQpUcmFjeQ0KDQpPbiBNYXJjaCAyOSwgMjAyMiA3OjAxOjQ3IFBNIEVEVCwgUmljaGFyZCBLYXllIDxya2F5ZUBpbnZhbHVhYmxlLmNvbTxtYWlsdG86cmtheWVAaW52YWx1YWJsZS5jb20+PiB3cm90ZToNCj5XaGlsZSBJJ20gcG9raW5nIGF3YXkgYXQgdGhpcywgSSB0aG91Z2h0IEknZCBjaGVjayB3aXRoIHRoZSBjb2xsZWN0aXZlIHdpc2RvbS4gSSd2ZSBmb3VuZCBhIHNpdHVhdGlvbiB3aGVyZSBJIHdhbnQgdG8gdGFrZSBhZHZhbnRhZ2Ugb2YgQ1VSU09SVE9YTUwgYW5kIFhNTFRPQ1VSU09SLiBUaGUgaXNzdWUgSSdtIHJ1bm5pbmcgaW50byBpcyBpZiB0aGUgY3Vyc29yIGJlaW5nIGNvbnZlcnRlZCB0byBYTUwgaGFzIGZpZWxkcyB3aXRoIE5VTEwgdmFsdWVzIGluIHRoZSBmaXJzdCByZWNvcmQsIHRoaXMgYXBwZWFycyB0byBhZmZlY3QgdGhlIHJlc3VsdGluZyBYTUxUT0NVUlNPUiBjb252ZXJzaW9uLiBBbnkgTlVMTCBjb2x1bW5zIHdpbGwgYmUgbWlzc2luZyBmcm9tIHRoZSByZXN1bHQgY3Vyc29yIGV2ZW4gaWYgbGF0ZXIgcm93cyBpbiB0aGUgWE1MIGNvbnRhaW4gdmFsdWVzLiBUaGUgaGFja3kgd29ya2Fyb3VuZCBzZWVtcyB0byBiZSBJIGhhdmUgdG8gbWFrZSBzdXJlIHRoYXQgdGhlIGZpcnN0IHJlY29yZCBoYXMgbm9uLW51bGwgdmFsdWVzIGZvciBhbGwgY29sdW1ucyBJIHdhbnQgaW4gdGhlIGN1cnNvci4gQW55b25lIGhhdmUgYW55IG90aGVyIG1hZ2ljIHRyaWNrcz8NCj4NCj5USUENCj4NCj4tLQ0KPg0KPnJrDQo+DQo+DQo+DQo+LS0tIFN0cmlwTWltZSBSZXBvcnQgLS0gcHJvY2Vzc2VkIE1JTUUgcGFydHMgLS0tDQo+bXVsdGlwYXJ0L2FsdGVybmF0aXZlDQo+IHRleHQvcGxhaW4gKHRleHQgYm9keSAtLSBrZXB0KQ0KPiB0ZXh0L2h0bWwNCj4tLS0NCj4NCltleGNlc3NpdmUgcXVvdGluZyByZW1vdmVkIGJ5IHNlcnZlcl0NCg0KX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18NClBvc3QgTWVzc2FnZXMgdG86IFByb0ZveEBsZWFmZS5jb208bWFpbHRvOlByb0ZveEBsZWFmZS5jb20+DQpTdWJzY3JpcHRpb24gTWFpbnRlbmFuY2U6IGh0dHBzOi8vbWFpbC5sZWFmZS5jb20vbWFpbG1hbi9saXN0aW5mby9wcm9mb3g8aHR0cHM6Ly9wcm90ZWN0LXVzLm1pbWVjYXN0LmNvbS9zL3JSRklDbjVZb2dJS2w4WGltcjd5UT9kb21haW49bWFpbC5sZWFmZS5jb20+DQpPVC1mcmVlIHZlcnNpb24gb2YgdGhpcyBsaXN0OiBodHRwczovL21haWwubGVhZmUuY29tL21haWxtYW4vbGlzdGluZm8vcHJvZm94dGVjaDxodHRwczovL3Byb3RlY3QtdXMubWltZWNhc3QuY29tL3MvOGVTQUNvMnYwamhCbE9ESW9yT3pJP2RvbWFpbj1tYWlsLmxlYWZlLmNvbT4NClNlYXJjaGFibGUgQXJjaGl2ZTogaHR0cHM6Ly9sZWFmZS5jb20vYXJjaGl2ZXM8aHR0cHM6Ly9wcm90ZWN0LXVzLm1pbWVjYXN0LmNvbS9zLzBXVjZDcFlScWt1dlFZOVM3cjA5Zj9kb21haW49bGVhZmUuY29tPg0KVGhpcyBtZXNzYWdlOiBodHRwczovL2xlYWZlLmNvbS9hcmNoaXZlcy9ieU1JRC81ODNCRDE3MC02OUVFLTQzMzctOTVGNi0wODNDMjlCQjA1QUFAcG93ZXJjaHVyY2guY29tPGh0dHBzOi8vcHJvdGVjdC11cy5taW1lY2FzdC5jb20vcy93STlCQ3F4VnJsVHpMcTFIdkpnWUY/ZG9tYWluPWxlYWZlLmNvbT4NCioqIEFsbCBwb3N0aW5ncywgdW5sZXNzIGV4cGxpY2l0bHkgc3RhdGVkIG90aGVyd2lzZSwgYXJlIHRoZSBvcGluaW9ucyBvZiB0aGUgYXV0aG9yLCBhbmQgZG8gbm90IGNvbnN0aXR1dGUgbGVnYWwgb3IgbWVkaWNhbCBhZHZpY2UuIFRoaXMgc3RhdGVtZW50IGlzIGFkZGVkIHRvIHRoZSBtZXNzYWdlcyBmb3IgdGhvc2UgbGF3eWVycyB3aG8gYXJlIHRvbyBzdHVwaWQgdG8gc2VlIHRoZSBvYnZpb3VzLg0K--- StripMime Report -- processed MIME parts ---multipart/alternative text/plain (text body -- kept) text/html---
Let’s try this again. (Stupid Outlook…)
That’s another possibility. Thanks, Tracy. 😊
The original data source is coming from a SQL backend so I’ve just added some ISNULL calls around the fields which matter to the CURSORTOXML/XMLTOCURSOR calls.
--
rk
From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Tracy Pearson Sent: Tuesday, March 29, 2022 7:33 PM To: profoxtech@leafe.com Subject: Re: CURSORTOXML and NULLs
I tend to create the cursor and use flags to append to the existing cursor.
Tracy
On March 29, 2022 7:01:47 PM EDT, Richard Kaye <rkaye@invaluable.commailto:rkaye@invaluable.com> wrote: While I'm poking away at this, I thought I'd check with the collective wisdom. I've found a situation where I want to take advantage of CURSORTOXML and XMLTOCURSOR. The issue I'm running into is if the cursor being converted to XML has fields with NULL values in the first record, this appears to affect the resulting XMLTOCURSOR conversion. Any NULL columns will be missing from the result cursor even if later rows in the XML contain values. The hacky workaround seems to be I have to make sure that the first record has non-null values for all columns I want in the cursor. Anyone have any other magic tricks?
You could consider your TSQL query to give you the XML in the initial query.
using :
Select ....
FOR XML PATH('NameOfDataHere'), ROOT('NameOfDataHere'), ELEMENTS
When you name the columns, put in the XML element path/name
c.ClientCity as "billTo/address/city",
On Tue, Mar 29, 2022 at 6:45 PM Richard Kaye rkaye@invaluable.com wrote:
Let’s try this again. (Stupid Outlook…)
That’s another possibility. Thanks, Tracy. 😊
The original data source is coming from a SQL backend so I’ve just added some ISNULL calls around the fields which matter to the CURSORTOXML/XMLTOCURSOR calls.
--
rk
From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Tracy Pearson Sent: Tuesday, March 29, 2022 7:33 PM To: profoxtech@leafe.com Subject: Re: CURSORTOXML and NULLs
I tend to create the cursor and use flags to append to the existing cursor.
Tracy
On March 29, 2022 7:01:47 PM EDT, Richard Kaye <rkaye@invaluable.com mailto:rkaye@invaluable.com> wrote: While I'm poking away at this, I thought I'd check with the collective wisdom. I've found a situation where I want to take advantage of CURSORTOXML and XMLTOCURSOR. The issue I'm running into is if the cursor being converted to XML has fields with NULL values in the first record, this appears to affect the resulting XMLTOCURSOR conversion. Any NULL columns will be missing from the result cursor even if later rows in the XML contain values. The hacky workaround seems to be I have to make sure that the first record has non-null values for all columns I want in the cursor. Anyone have any other magic tricks?
[excessive quoting removed by server]
-
Richard Kaye -
Stephen Russell -
Tracy Pearson