If you’re using FOR XML in SQL Server to output your query results as XML, you might find yourself in the situation where the XML returned has no root element. Fortunately adding a root element is easy, as I’ll show you in this article.
Category: DBMS
Database Management Systems
3 Ways to Unquote a JSON Value in MySQL
In MySQL, we have several options when it comes to removing the quotes from a JSON value.
For example, normally if we return a string value from a JSON document, we would get that value along with its quotes. But sometimes we might not want the quotes. Below are three methods we can use to unquote a JSON value in MySQL.
Continue readingFix “MINVALUE (…) must be less than MAXVALUE (…)” When Creating a Sequence in PostgreSQL
If you’re getting an error that reads something like “MINVALUE (1) must be less than MAXVALUE (1)” in PostgreSQL when you’re trying to create a sequence, it’s probably because your sequence’s minimum possible value is higher than the maximum value.
To fix this issue, be sure that the sequence’s maximum value is greater than the minimum value.
Continue reading2 Ways to Apply a Sequence to a Table in SQL Server
In SQL Server, we can create sequence objects in order to generate a range of numbers that increment with each call to the sequence.
Although sequences are similar to identity columns, they are different in that they are generated independently of any table. It’s up to the application to generate the next sequence number before inserting it into the table. Therefore, we can apply a sequence to a table in the same way we would insert any other data into a table.
Here are two ways to apply sequence numbers into a table in SQL Server.
Continue readingFix “WRONGTYPE Operation against a key holding the wrong kind of value” when using ZLEXCOUNT in Redis
If you get an error that reads “WRONGTYPE Operation against a key holding the wrong kind of value” when using the ZLEXCOUNT command in Redis, it’s probably because you’re passing a key with the wrong data type.
To fix this issue, be sure that you pass a sorted set to the ZLEXCOUNT command.
Understanding the JSON_SCHEMA_VALIDATION_REPORT() Function in MySQL
In MySQL, the JSON_SCHEMA_VALIDATION_REPORT() function validates a JSON document against a JSON schema and returns a report of the outcome of that validation. The report is returned as a JSON document.
If the document is valid, only one field is returned: "valid" : true. If the document is not valid, a more extensive report is provided.
7 Functions to Format a Number to 2 Decimal Places in MySQL
A common task when working with numbers is to format them to a certain amount of decimal places. Two decimal places seems to be the most common format, but we can format to any number of decimal places we want. MySQL provides us with a number of functions that can help us achieve this.
Below are seven functions that can be used to format a number to two decimal places in MySQL.
Continue readingHow JSON_SCHEMA_VALID() Works in MySQL
In MySQL, the JSON_SCHEMA_VALID() function validates a JSON document against a JSON schema. It returns true (1) if the document is valid, and false (0) if it is not.
Get a List of Sequences in a SQL Server Database (T-SQL)
In SQL Server, we can use the sys.sequences system catalog view to return a list of sequence objects in the current database.
Fix Error Msg 3625 “‘Inline XSD for FOR XML EXPLICIT’ is not yet implemented.” in SQL Server
If you’re getting error Msg 3625 in SQL Server that reads “‘Inline XSD for FOR XML EXPLICIT’ is not yet implemented“, it’s because you’re trying to add an inline schema to an XML document that you’re generating using EXPLICIT mode with the FOR XML clause.
As the message alludes to, SQL Server doesn’t yet support inline XSD schemas when using EXPLICIT mode of the FOR XML clause (at least, not at the time of this writing).
To fix this issue, either use a different mode to generate the XML with an inline schema (specifically, use either AUTO or RAW mode), or don’t generate an inline schema at all (i.e. remove XMLSCHEMA from the query).