Very nice @Solomon. Thank you. I was wondering if you could suggest a way to return the string from the XML as NVARCHAR(MAX)?

I'm thinking now to return the string as XML to C# and not deal with this conversion. The purpose of converting to NV(MAX) was so the string could be returned to C# as JSON. Using XML seems preferrable in this case

Xml worked. Ha, thanks again :)

Hi @SteveC . Yer welcome, glad it worked for you. However, if you are not viewing it in SSMS, then why bother with any of this in the first place? Simply getting the return value from OBJECT_DEFINITION(OBJECT_ID(N'schema_name.object_name')) should be sufficient. Meaning, converting that output to XML is most likely an unnecessary step for your purposes; the only reason this method is being used in this context is to preserve formatting while viewing in SSMS.

It's for codegen which needs the formatting too. Returning as string would then require fixing the format. Your answer helped me move forward. Thanks again

@SteveC Again, glad it's working, but I'm still confused ;-). Returning the output of OBJECT_DEFINITION() shouldn't remove any formatting, and should be identical to the XML except it wouldn't start with <Code><![CDATA[ and end with ]]></Code>. This question is specific to viewing the definition within SSMS, which flattens everything to one row (Results to Grid), or cuts off at 8k characters (using PRINT). Results to Text used to have a lower limit but now can return up to 2 MB: SET NOCOUNT ON; SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_helptext'));. XML, however, can be unlimited.

Hi there - I've read many of your articles and web sites. Thank you for everything. So if you run "select OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_helptext')) obj_def for json path;" are you able to parse the JSON?

If I copy the results of that query into an online JSON parser it returns an error. Based on that I didn't even bother sending it back to C#. Seemingly no matter what I try when I copy/paste the results I'm not able to get the original formatting back. Perhaps replacing /t/n would work. But then Xml sidesteps the whole issue and works perfectly :). It's for a SQL code generator built using T4 templates. It uses the OpenAPI swagger.json file produced by a .NET Core WebAPI and it autogenerates all of the boilerplate code needed to support stored procedures.

If the endpoint already exists it autogenerates a script containing the procedure definitions, synonyms, etc. and enables application version control to be applied to stored procedures as extended properties

@SteveC Also, I'm still not sure why you aren't just returning SET NOCOUNT ON; SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_helptext')) AS [def]; as NVARCHAR(MAX). It's possible I'm missing something as I'm not familiar with either OpenAPI swagger.json files or the .NET Core WebAPI. It just seems like wrapping up as either XML or JSON only to parse it back to a string is two additional, yet unnecessary, steps.

Ok I'm not too familiar with this but this does seem chatlike

@SteveC Hey. So, I'm not meaning to drag this out since you said that it's working for you. I'm just saying that it appears to me that using either XML or JSON to get the definition back to C# is unnecessary overcomplication. I could be wrong as I'm not seeing what you're seeing, but this question was addressing behavior specific to SSMS; returning OBJECT_DEFINITION() should work in all other cases.

Solomon it's much appreciated! It's my pleasure to answer any questions you might have. It's taking me a bit of time to backtrack and test

I agree with everything you've written. It seems possible I prematurely dumped JSON when the online parser choked on copy/paste

@SteveC That did throw me for a loop as well at first. But the parser I tried gave me the character position that it didn't like and it was the newline added by SSMS, so I removed it and it next complained about the next newline. That's when I tried storing the JSON output as NVARCHAR(MAX). But still, why even use JSON in the first place?

Hey Ok, you know what? It does work when returned as JSON

Why JSON? JSON can be read/written from directly in SQL Server so I use it to skip custom coding of data access in C# and this has very good system properties. I've mentioned this many times on SSC but haven't really put it out there I guess.

I've made a few videos which explain it and it's an open source library. At some point I would very much like to give you a personal run through if you're Ok with that. There's a lot to explain and I know you're the author the C# CLR library which is amazing

Sticking with the native types is always an option if necessary

It's a tiny library about 25Kb currently. The filters short circuits the http pipeline using a ResourceFilter triggered by a TypeFilter. It reads the one-way stream and sends generic JSON to the db. github.com/The-Cantor-Set/JsonAutoService

It allows you to create Controllers which are only defined by Attribute objects yet can be fully parameterized.

@SteveC Sounds and looks interesting. Sure, we should do that sometimes. Things are kinda crazy busy at the moment, but hopefully that won't last forever ;-). I didn't see any contact info for you either here or on GitHub, but I can be reached via: sqlquantumleap.com/contact

Crazy in a good way I hope -- thank you Solomon. Thank you for helping out with this particular issue and for everything else. I'll definitely follow up with you. Maybe after this last bit of automation is complete :) My best contact is [email protected] or GitHub too. Cheers