January 21, 2014 at 12:24 am
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!
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply