SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Query multiple XML data values in ntext column

Query multiple XML data values in ntext column

Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 24

I have been searching on this for quite some time (in this forum and others), but have not been able to figure out how to correctly query what I need. This is my first post here so I hope I provide everything, but I will gladly answer any questions you have :-)

Basically, what I would like to do is to query what a couple of values are stored as. The data looks like XML, but it is stored in an ntext column. A specific example that I would like to get are the Code, Name, and XML values: "SQL Server" and "Organization".

The data is stored by a separate program, and I unfortunately do not have control over how the data is written or the design of the database.

The only query I have been able to figure out so far is the following:

select Code, Name,
CAST(ConnectionInformation as xml).query('ArrayOfEntitySetting/EntitySetting[@Name="SQL Server"]') as SQLServer,
CAST(ConnectionInformation as xml).query('ArrayOfEntitySetting/EntitySetting[@Name="Organization"]') as Organization
from XMLTest

This "works" but the results it returns are messy:


ABC Company


<EntitySetting Name="SQL Server"><Value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">SQLServ1</Value></EntitySetting>


<EntitySetting Name="Organization"><Value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">ABC</Value></EntitySetting>

Ideally, the simpler (shorter) I can keep the query the better as I will be asking others to run it for retrieving data from their environments; however, I am fine with anything that works. If you can provide any pointers for me on how to do this, that would be great. I do not need a full-fledged answer, but I also won't reject one :-P

Below is the code that can create a simplified example of the table I am looking at as well as some data that stores:

[ID] [uniqueidentifier] NOT NULL,
[Code] [nvarchar](100) NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[ConnectionInformation] [ntext] NULL,

,'ABC Company'
,'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfEntitySetting xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<EntitySetting Name="SQL Server">
<Value xsi:type="xsd:string">SQLServ1</Value>
<EntitySetting Name="Database Name">
<Value xsi:type="xsd:string">DB1</Value>
<EntitySetting Name="Organization">
<Value xsi:type="xsd:string">ABC</Value>
<EntitySetting Name="Windows Authentication">
<Value xsi:type="xsd:boolean">true</Value>
<EntitySetting Name="SQL User">
<Value xsi:type="xsd:string" />
<EntitySetting Name="SQL Password">
<Value xsi:type="xsd:base64Binary" />

As a side note (not sure if this is helpful), if I query the sys.xml_schema_namespaces catalog view for this database, I do see the following namespaces listed:


Please let me know if I can provide any other helpful details. TIA!


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum