Oracle select xml decode code#

Another issue is the way the parser handles special characters that appear in an element or attribute value. Unfortunately, the XML parser is not always consistent in entitizing characters, as demonstrated by the fact that only one linefeed is stored as . The process of preserving certain characters with these special symbols is known as entitization. When you retrieve the XML value directly, these special characters are automatically displayed in a readable format. Tabs and linefeeds are just two of the special characters that need to be preserved in this way, as the above example demonstrates, with tabs being saved as and linefeeds saved as When the XML parser stores characters such as tabs and linefeeds within an XML document or fragment, it must do so in a way that preserves them throughout the parser’s normalization process, allowing these elements to be maintained whether being stored or retrieved or whether being converted to or from XML. NOTE: If I were to try to run this example without converting the original XML fragment, or converting the fragment without specifying the third argument in the CONVERT() function, the tabs and linefeeds would not be preserved, and the two SELECT statements would return the string without those tabs and linefeeds. The following set of Transact-SQL statements are similar to the last example, except that I’ve included a second SELECT statement to retrieve the value and I’ve modified the second instance of the CONVERT() function so it doesn’t include the third argument ( 1): To better understand what’s happening, take a look at another example. What this points to is that SQL Server handles certain components of an XML fragment-whether converting to or from the XML type-differently from the basic elements, attributes, and their values.

XML EntitizationĪs the last example demonstrates, you can use the CONVERT() function to preserve characters that the XML parser normally ignores, such as spaces and tabs between elements. Let’s take a closer look at this process so you can better understand what’s happening behind the scenes when you convert XML data. Now the string includes the tabs and linefeeds as they exist in the original XML fragment.

The SELECT statement returns the following results: The way to get around this is to use the CONVERT() function to explicitly cast the string to the XML type, as I do in the following example:Īs you can see, I include a third argument in the CONVERT() function that specifies these elements be preserved.

What is essentially happening here is that SQL Server is implicitly converting a string value to the XML type, which means that the tabs and linefeeds are not being preserved during that assignment. Notice that I assign the value simply by setting to equal the XML fragment, enclosed in single quotes. The problem is not with how I convert the value, but with the way I assign the XML fragment to that variable. However, although I take this approach, the value saved to the variable does not preserve these elements, as shown in the results returned by the SELECT statement: However, if you’re running your script only against SQL Server, you can take advantage of the CONVERT() function’s support for additional options, which let you better refine the conversion process.įor instance, in the following example I use tabs between the parent and child elements in the XML fragment:īecause the XML fragment contains these new elements, you might expect that you can simply add the third argument to the CONVERT() function to preserve the tabs and linefeeds.

Oracle select xml decode code#

If you want to run your script against another database system, you have to modify your code to conform to that system’s specifications. That’s not the case with the CONVERT() function, which is specific to Transact-SQL in SQL Server. Because the CAST() function conforms to ANSI specifications, you can use it with any database systems that conform to those standards. One reason you might want to use CAST()is if you plan to run your SQL script against a database management system in addition to SQL Server and that system doesn’t support implicit conversions. You might be wondering why you would explicitly cast string or binary data to the XML type if SQL Server handles such conversions implicitly. Once again, the SELECT statement returns the XML fragment as it is assigned to the variable: Notice that I follow the XML data type argument with a comma, then I specify the variable.