Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
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, April 08, 2014 10:02 AM
Points: 1, Visits: 22
Hello,

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:


Code:
ABC

Name:
ABC Company

SQLServer:

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

Organization:

<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

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


CREATE TABLE [dbo].XMLTest(
[ID] [uniqueidentifier] NOT NULL,
[Code] [nvarchar](100) NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[ConnectionInformation] [ntext] NULL,
)


INSERT INTO [dbo].[XMLTest]
([Code]
,[Name]
,[ConnectionInformation])
VALUES
('ABC'
,'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>
<EntitySetting Name="Database Name">
<Value xsi:type="xsd:string">DB1</Value>
</EntitySetting>
<EntitySetting Name="Organization">
<Value xsi:type="xsd:string">ABC</Value>
</EntitySetting>
<EntitySetting Name="Windows Authentication">
<Value xsi:type="xsd:boolean">true</Value>
</EntitySetting>
<EntitySetting Name="SQL User">
<Value xsi:type="xsd:string" />
</EntitySetting>
<EntitySetting Name="SQL Password">
<Value xsi:type="xsd:base64Binary" />
</EntitySetting>
</ArrayOfEntitySetting>')


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:

http://www.w3.org/2001/XMLSchema
http://schemas.microsoft.com/sqlserver/2004/sqltypes
http://www.w3.org/XML/1998/namespace

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