Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Query multiple XML data values in ntext column Expand / Collapse
Posted Tuesday, January 21, 2014 12:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 10, 2015 10:24 AM
Points: 1, 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="" xsi:type="xsd:string">SQLServ1</Value></EntitySetting>


<EntitySetting Name="Organization"><Value xmlns:xsi="" 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

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="" xmlns:xsi="">
<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!
Post #1532878
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse