Query multiple XML data values in ntext column

• DisgracedCosmonaut

  SSC Enthusiast

  Points: 101

  More actions

  January 21, 2014 at 12:24 am

  #396134

  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!