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 post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy