Tag Info

New answers tagged

2

Here is a working FOR XML EXPLICIT example. They are a bit harder to code, but I tend to build them up, section by section so they're not so bad: USE tempdb GO SET NOCOUNT ON GO IF OBJECT_ID('dbo.Borrowers') IS NOT NULL DROP TABLE dbo.Borrowers CREATE TABLE dbo.Borrowers ( ID INT IDENTITY PRIMARY KEY, FirstName ...


4

If you do 'NewID' as [field/@id] you will get a field element with an id attribute. On the next line you add B.ID as [field] ot get the value of ID as the node value to the field node you created on the line before. <field id="NewID">1</field> After that you want a new field node and to create that you can use a node without a name that has no ...


1

Apparently, creation of CDATA nodes is possible only in the FOR XML EXPLICIT mode. Moreover, Microsoft implementation of XML does not respect them, in accordance with W3C recommendations. Here is a link: How to get [CDATA] with FOR XML PATH? So, if you truly need this, you'll have to write an ugly monster, and don't even think about the TYPE option - it ...


2

If you don't want to use xsi:nil you have to decide what value you want to have in the XML when the value in the table is null. For strings it might be appropriate with an empty string. For integers you might want to use 0 or -1 or perhaps convert the column data to a string first and use an empty string for integers as well. You have to decide what value ...


3

There a number of approaches to tuning XML queries in SQL Server. Property promotion is a good one, but I also regularly use the following: XML Indexes Best practice syntax XML Schema Collection Full-text Indexing XML Indexes XML Indexes can transform XML query performance, but at a cost. Pre-SQL Server 2012, they come in two types, primary XML ...


3

You should have a look at Property Promotion in XML Best Practices for Microsoft SQL Server 2005. Create a user defined scalar valued function that extracts the value you need and use the function as a computed column in your table. You can persist the column of you like and you can create a regular index on the column. You don't have to persist the column ...


0

You mention you have tried SQL*Plus and Toad; what is your client operating system, Windows? Unix? See NLS_OS_CHARSET Environment Variable that is an environment variable set on your client to a value that your client understands and supports. If you are on Unix, invoke the locale command and see what LANG or LC_ALL is set to; you may need to set ...


1

The chinese characters are decoded correctly with utl_i18n.unescape_reference. In fact they just aren't displayed properly in your query result which might not support those special characters. You can confirm that with this SQL Fiddle. It is the client which is respsonsible to display the characters appropriately. If the client can not display a ...


1

You might have wanted this: WITH XML_DATA AS (SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?> <response> <phone> <number>859111</number> <contactModes> <mode>AUTO_DIAL</mode> <mode>AUTO_TEXT</mode> <mode>MANUAL_DIAL</mode> ...


0

I found the answer myself after reading through loads of documentation from Oracle. The answer comes in two parts. First, my original question can be solved by using XMLTable: SELECT x.ID AS "MEASUREMENT_ID", x.GUID AS "MEASUREMENT_GUID", a.COLUMN_VALUE AS "MESSAGE" FROM SPERF_XML_RAW x, ...



Top 50 recent answers are included