Mixing INNER and OUTER joins

I've encountered this problem multiple times in the past and I have a solution but wonder if there might be a more elegant method of achieving the same result...

Take the following example:

SELECT * FROM [User]
LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

Users may or may not have a profile (but never more than one). A profile may or may not have a department (but never more than one).

Now, this will return only users that have a profile even though an outer join has been used. What I really want is to return all users and include their profile and department details but only when the profile has a department.

The solution I have used in the past is:

SELECT * FROM [User]
LEFT OUTER JOIN
(
SELECT *
FROM [Profile]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
) [ProfileDepartment] ON [ProfileDepartment].[UserId] = [User].[UserId]

The trouble here is that I've lost the ability to reference department and profile independantly in the outer query. Also, more complex scenarios can also become horribly complex if this needs to be done multiple times in the same query.

I could do this:

SELECT * FROM [User]
LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId] AND [Profile].[DepartmentId] IS NOT NULL
LEFT OUTER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

But again I feel that the intention is not at all clear. I want to inner join department to profile because I'm only interested in profiles with a department and departments referenced by a profile.

I would like to be able to specify that the departments should be inner joined to profiles and whichever profiles remain get outer joined to users whilst retaining department and profile as seperate entities within the query.

Is there any way to use brackets to indicate an order of precedance to the logical joins within the from clause?

Daniel

I think that this is pretty much the answer, though there is an easier way to write it

SELECT * FROM [User]
LEFT OUTER JOIN
(
SELECT *
FROM [Profile]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
) [ProfileDepartment] ON [ProfileDepartment].[UserId] = [User].[UserId]

You can nest the joins to get the effect you want:

SELECT *
FROM [User]
LEFT OUTER JOIN [Profile]
INNER JOIN [Department] --this limits the department to the context of a Profile only!
ON [Department].[DepartmentId] = [Profile].[DepartmentId]
ON [Profile].[UserId] = [User].[UserId]

However, you have the same limitation that you could not in another join reference Department. You could nest in other tables to to get to the right combination, perhaps (it can get really messy fast if you have lots of tables to join :) So if you actually need to refer to the department in that context in a later join, then the two left outer joins might be the correct thing to do

SELECT *
FROM [User]
LEFT OUTER JOIN [Profile]
ON [Profile].[UserId] = [User].[UserId]
AND [Profile].[DepartmentId] IS NOT NULL
LEFT OUTER JOIN [Department]
ON [Department].[DepartmentId] = [Profile].[DepartmentId]

I don't see a problem with that syntax at all. You are correct that it is not as clear what your intention is, but, if you are joining department to multiple tables, that might be the case.

I highlighted in that context because if you need department in another context, it is valid to reference it again, as some alias:

SELECT *
FROM [User]
LEFT OUTER JOIN [Profile]
INNER JOIN [Department] --this limits the department to the context of a Profile only!
ON [Department].[DepartmentId] = [Profile].[DepartmentId]
ON [Profile].[UserId] = [User].[UserId]
INNER JOIN Department as otherDepartment
ON User.department = otherDepartment.DepartmentId

Or whatever. It is all in how you need to use the data.

|||

Hi Louis,

Thanks for the info; the nesting of joins is new syntax to me... Looks like I'm going to have to revisit my T-SQL code structure standards now!

I've tried the nexted join approach for the specific scenario I'm working on and it seems to work perfectly and also reads more clearly to me than the other two options.

I've also tried to extend it more than I need right now and it looks like you can reference the nested joined table in a different context, e.g.:

SELECT *

FROM [User]

LEFT OUTER JOIN [Profile]

INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

ON [Profile].[UserId] = [User].[UserId]

LEFT OUTER JOIN [BusinessUnit] ON [BusinessUnit].[BusinessUnitId] = [Department].[BusinessUnitId]

AND [BusinessUnit].[BusinessUnitId] = [User].[BusinessUnitId]

I think you were saying that the above example would not work because the department table is only visible inside the scope of the "profile" join but the above example does work OK. Have I misunderstood what you were saying or could this be a change in SQL 2005?

Daniel

mission impossible: How to protect a Sql Server (with VB.NET) from somebody who as adminis

Hi,
I'm sorry for the probable cross-posting, but I need a solution that can be
either via Sql Server (preferable) or via .NET...
In one of our commpany's we're having a huge problem with a crazy situation:
A lot of people which are working for the company are also supplier to the
company. So they are manage their own delivery's, and their own payments...
They used to have applications in Access, but if they want to (and they do,
hehe) thay can change their own payments they earn. Some of them have even
administration rights, and full access to the Sql Server Database (it
personell etc).
So what I need is:
- make the application in VB.NET, without giving them the source offcourse
- a protection so that, even with administrator rights, they can't change
their own data. I was thinking about something like a checksum-algeorithm.
Does anybody has experience with this? how should I implement an
checksum-algorithm? What other options do I have?
Any help will be really appreciated!!
Thanks a lot in advance,
PieterYou can't protect your data against users with the SYSADMIN role. What
you should be able to do is deny admin level access to the users. You
didn't explain why they need that. If it's an issue that they need to
administer some things and not others then separate off the restricted
data into another server or instance to which they don't need admin
level access.
If it's just an issue of them historically having had an admin user
name and password, then change the user name or password and/or issue
new user names as appropriate.
--
David Portas
SQL Server MVP
--|||Pieter skrev:
> In one of our commpany's we're having a huge problem with a crazy situation:
> A lot of people which are working for the company are also supplier to the
> company. So they are manage their own delivery's, and their own payments...
Hi Pieter,
I won't provide you with the answer you want, I just want to recommend
you and the company you work for to clear up the situation instead of
trying to patch it with some software solution... The people that are
suppliers
should obviously NOT be authorized to change/access such data.
Hope you'll sort this situation out!
Best regards,
/impslayer, aka Birger Johansson|||The problem is: the IT staff that must do the maintenance of the SQL Server
really needs the sysadmin...
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1131018930.858186.322060@.z14g2000cwz.googlegroups.com...
> You can't protect your data against users with the SYSADMIN role. What
> you should be able to do is deny admin level access to the users. You
> didn't explain why they need that. If it's an issue that they need to
> administer some things and not others then separate off the restricted
> data into another server or instance to which they don't need admin
> level access.
> If it's just an issue of them historically having had an admin user
> name and password, then change the user name or password and/or issue
> new user names as appropriate.
> --
> David Portas
> SQL Server MVP
> --
>|||Hehe well I do know that, and everybody concerned does, but it's just not
this simple...
It's foolish to alow the people of the staff to become their own suppliers.
But they did it, and going back will be hard and even impossible.
We just can try to make it as hard as possible, and have at least a much
smaller chance on fraude...
"impslayer" <impslayer@.hotmail.com> wrote in message
news:1131019112.339033.60950@.o13g2000cwo.googlegroups.com...
> Pieter skrev:
>> In one of our commpany's we're having a huge problem with a crazy
>> situation:
>> A lot of people which are working for the company are also supplier to
>> the
>> company. So they are manage their own delivery's, and their own
>> payments...
> Hi Pieter,
> I won't provide you with the answer you want, I just want to recommend
> you and the company you work for to clear up the situation instead of
> trying to patch it with some software solution... The people that are
> suppliers
> should obviously NOT be authorized to change/access such data.
> Hope you'll sort this situation out!
> Best regards,
> /impslayer, aka Birger Johansson
>|||Rather than attempt to change their rights for their data, consider putting
a mechanism into place that stores historical data so you have a way to
prove who change the data and when. This, in the long run, will have a
greater effect, as you can say "you change your payments from $1000 to $2000
on December 5, 2005 at 12:35:36.001 AM", which is against our rules. Rather
than locking them out, logging changes and even sending alerts to key staff
when certain fields are changed -- priceless.
This can certainly be done in SQL Server with triggers, but realize that the
sysadmin can change or delete triggers. If you put a VB.NET front end, you
have the power to do what you want, as long as they cannot circumvent the
front end you create. If they have table access, you are toast.
I cannot give you a firm direction without a better understanding of the
product, which is beyond the scope of an open forum. Now, if you want to
stop changes to certain fields, you can add a trigger that refuses updates
on that field. Realize they can disable it, however.
One more option: Have a second copy of the data that you do not want them to
change. You can then compare deltas and find cases where they are playing
games and then present them with the evidence.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***********************************************
Think Outside the Box!
***********************************************
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:uAtUqlG4FHA.4076@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm sorry for the probable cross-posting, but I need a solution that can
> be either via Sql Server (preferable) or via .NET...
> In one of our commpany's we're having a huge problem with a crazy
> situation: A lot of people which are working for the company are also
> supplier to the company. So they are manage their own delivery's, and
> their own payments...
> They used to have applications in Access, but if they want to (and they
> do, hehe) thay can change their own payments they earn. Some of them have
> even administration rights, and full access to the Sql Server Database (it
> personell etc).
> So what I need is:
> - make the application in VB.NET, without giving them the source offcourse
> - a protection so that, even with administrator rights, they can't change
> their own data. I was thinking about something like a checksum-algeorithm.
> Does anybody has experience with this? how should I implement an
> checksum-algorithm? What other options do I have?
> Any help will be really appreciated!!
> Thanks a lot in advance,
>
> Pieter
>|||SQL Server permissions are incredibly configurable. You don't have to make
people administrators to enable them to perform specific types of tasks. You
can create accounts that have these specific permissions and no other.
--
HTH,
Kevin Spencer
Microsoft MVP
.Net Developer
A watched clock never boils.
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:eSfAs4G4FHA.3292@.tk2msftngp13.phx.gbl...
> The problem is: the IT staff that must do the maintenance of the SQL
> Server really needs the sysadmin...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1131018930.858186.322060@.z14g2000cwz.googlegroups.com...
>> You can't protect your data against users with the SYSADMIN role. What
>> you should be able to do is deny admin level access to the users. You
>> didn't explain why they need that. If it's an issue that they need to
>> administer some things and not others then separate off the restricted
>> data into another server or instance to which they don't need admin
>> level access.
>> If it's just an issue of them historically having had an admin user
>> name and password, then change the user name or password and/or issue
>> new user names as appropriate.
>> --
>> David Portas
>> SQL Server MVP
>> --
>|||Ok, here is a really ugly solution...
Encrypt and decrypt the data in your application. Store encrypted data in
the database, at least for the fields in question. This adds a lot of
overhead to your application, but as long as you are not dealing with large
amounts of data you may still be ok. Only those with access to the
application will be able to store the data in the proper encrypted format.
Note, in order for such an encryption to work you have to make it complex
enough so that "1000" will be encrypted differently for every record. i.e
make the primary key part of your encryption algorithm.
Now, you probably need to have access to this data for reporting, in which
case you can store the critical values in both encrypted and plain text
form. A simple procedure in the application will find all the records that
were tampered with. Also, when it comes time to pay out this money, make
sure it is the encrypted value that is being used.
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:uAtUqlG4FHA.4076@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm sorry for the probable cross-posting, but I need a solution that can
be
> either via Sql Server (preferable) or via .NET...
> In one of our commpany's we're having a huge problem with a crazy
situation:
> A lot of people which are working for the company are also supplier to the
> company. So they are manage their own delivery's, and their own
payments...
> They used to have applications in Access, but if they want to (and they
do,
> hehe) thay can change their own payments they earn. Some of them have even
> administration rights, and full access to the Sql Server Database (it
> personell etc).
> So what I need is:
> - make the application in VB.NET, without giving them the source offcourse
> - a protection so that, even with administrator rights, they can't change
> their own data. I was thinking about something like a checksum-algeorithm.
> Does anybody has experience with this? how should I implement an
> checksum-algorithm? What other options do I have?
> Any help will be really appreciated!!
> Thanks a lot in advance,
>
> Pieter
>|||Thanks guys for the help and hints.
I guess I will indeed need to encrypt the values, or better: add a hash so
the values can be used in reports.
The only problem with reports is thatonce the report is exported, values can
be changed afterwurths... Some kind of solution may be to hash the total of
the report and print iton the report, and give the people that receive the
report a tool that recreates the hash to be able to verify it...
"Jim Underwood" <james.underwood@.fallonclinic.com> wrote in message
news:%23aB0Z6U4FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Ok, here is a really ugly solution...
> Encrypt and decrypt the data in your application. Store encrypted data in
> the database, at least for the fields in question. This adds a lot of
> overhead to your application, but as long as you are not dealing with
> large
> amounts of data you may still be ok. Only those with access to the
> application will be able to store the data in the proper encrypted format.
> Note, in order for such an encryption to work you have to make it complex
> enough so that "1000" will be encrypted differently for every record. i.e
> make the primary key part of your encryption algorithm.
> Now, you probably need to have access to this data for reporting, in which
> case you can store the critical values in both encrypted and plain text
> form. A simple procedure in the application will find all the records
> that
> were tampered with. Also, when it comes time to pay out this money, make
> sure it is the encrypted value that is being used.
>
> "Pieter" <pietercoucke@.hotmail.com> wrote in message
> news:uAtUqlG4FHA.4076@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> I'm sorry for the probable cross-posting, but I need a solution that can
> be
>> either via Sql Server (preferable) or via .NET...
>> In one of our commpany's we're having a huge problem with a crazy
> situation:
>> A lot of people which are working for the company are also supplier to
>> the
>> company. So they are manage their own delivery's, and their own
> payments...
>> They used to have applications in Access, but if they want to (and they
> do,
>> hehe) thay can change their own payments they earn. Some of them have
>> even
>> administration rights, and full access to the Sql Server Database (it
>> personell etc).
>> So what I need is:
>> - make the application in VB.NET, without giving them the source
>> offcourse
>> - a protection so that, even with administrator rights, they can't change
>> their own data. I was thinking about something like a
>> checksum-algeorithm.
>> Does anybody has experience with this? how should I implement an
>> checksum-algorithm? What other options do I have?
>> Any help will be really appreciated!!
>> Thanks a lot in advance,
>>
>> Pieter
>>
>|||It looks like you have a few technical solutions to work with, but I want to
stress one point...
Very often the proper solution to a problem like this is not technology, but
policy. This sounds like one of those cases.
While you can make it difficult for anyone to manipulate the data, you
should not have to go to these extents. With a system of this type, that
controls payments, very few should have access that allows them to change
data. Suppliers should not be managing the database that pays them, and
anyone who changes the data in anyway should be terminated on the spot.
Companies often look for technical solutions for bad business practices.
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:uAtUqlG4FHA.4076@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm sorry for the probable cross-posting, but I need a solution that can
be
> either via Sql Server (preferable) or via .NET...
> In one of our commpany's we're having a huge problem with a crazy
situation:
> A lot of people which are working for the company are also supplier to the
> company. So they are manage their own delivery's, and their own
payments...
> They used to have applications in Access, but if they want to (and they
do,
> hehe) thay can change their own payments they earn. Some of them have even
> administration rights, and full access to the Sql Server Database (it
> personell etc).
> So what I need is:
> - make the application in VB.NET, without giving them the source offcourse
> - a protection so that, even with administrator rights, they can't change
> their own data. I was thinking about something like a checksum-algeorithm.
> Does anybody has experience with this? how should I implement an
> checksum-algorithm? What other options do I have?
> Any help will be really appreciated!!
> Thanks a lot in advance,
>
> Pieter
>

Missing XML header that specifies the encoding

Hello,
In my solution I ned the xml output with including the XML header thet
specifies the encoding.
Here's the code:
CREATE XML SCHEMA COLLECTION MyNote AS
N'<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.w3schools.com" xmlns="http://
www.w3schools.com" elementFormDefault="qualified">
<xs:element name="note">
<xs:complexType>
<xs:sequence>
<xs:element name="to" type="xs:string"/>
<xs:element name="from" type="xs:string"/>
<xs:element name="heading" type="xs:string"/>
<xs:element name="body" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="HedingType" type="xs:string"/>
</xs:schema>'
GO
then input a valid xml
DECLARE @.x xml (dbo.MyNote)
SET @.x=N'<note xmlns="http://www.w3schools.com">
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this wend!</body>
</note>'
select @.x
Here's the output:
<note xmlns="http://www.w3schools.com">
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this wend!</body>
</note>
I need the XML header that specify the encoding of the document, like
this:
<?xml version="1.0" encoding="UTF-8"?>
<note xmlns="http://www.w3schools.com">
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this wend!</body>
</note>
Any ideas?
Thanks in advance!
JohnWell the heading is optional for UTF-8 documents. The heading's only useful
for serialised documents, how are you using the output?
Joe Fawcett (MVP - XML)
http://joe.fawcett.name
"johnk" <johnk_hansen@.hotmail.com> wrote in message
news:1170427960.566676.200010@.s48g2000cws.googlegroups.com...
> Hello,
> In my solution I ned the xml output with including the XML header thet
> specifies the encoding.
> Here's the code:
> CREATE XML SCHEMA COLLECTION MyNote AS
> N'<?xml version="1.0" encoding="UTF-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> targetNamespace="http://www.w3schools.com" xmlns="http://
> www.w3schools.com" elementFormDefault="qualified">
> <xs:element name="note">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="to" type="xs:string"/>
> <xs:element name="from" type="xs:string"/>
> <xs:element name="heading" type="xs:string"/>
> <xs:element name="body" type="xs:string"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="HedingType" type="xs:string"/>
> </xs:schema>'
> GO
> then input a valid xml
> DECLARE @.x xml (dbo.MyNote)
> SET @.x=N'<note xmlns="http://www.w3schools.com">
> <to>Tove</to>
> <from>Jani</from>
> <heading>Reminder</heading>
> <body>Don't forget me this wend!</body>
> </note>'
> select @.x
> Here's the output:
> <note xmlns="http://www.w3schools.com">
> <to>Tove</to>
> <from>Jani</from>
> <heading>Reminder</heading>
> <body>Don't forget me this wend!</body>
> </note>
> I need the XML header that specify the encoding of the document, like
> this:
> <?xml version="1.0" encoding="UTF-8"?>
> <note xmlns="http://www.w3schools.com">
> <to>Tove</to>
> <from>Jani</from>
> <heading>Reminder</heading>
> <body>Don't forget me this wend!</body>
> </note>
> Any ideas?
>
> Thanks in advance!
> John
>|||On 2 Feb, 17:46, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> Well the heading is optional for UTF-8 documents. The heading's only usefu
l
> for serialised documents, how are you using the output?
> --
> Joe Fawcett (MVP - XML)http://joe.fawcett.name
> "johnk" <johnk_han...@.hotmail.com> wrote in message
> news:1170427960.566676.200010@.s48g2000cws.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Vis sitert tekst -
I am using the query window in MS SQL Server Management Studio .|||John,
The processing instruction specifies the encoding when the XML is stored as
text. When you store an instance in an XML datatype, it is not stored as tex
t
but in binary XML format. Therefore, what's the point of specifying an XML
encoding? It would have no meaning.
In your case, I would suggest you convert your XML instance to a string type
and
add the processing instruction trhough string manipulation. You can do somet
hing
like this
DECLARE @.x xml (dbo.MyNote)
DECLARE @.strXML varchar(MAX)
SET @.x=N'<note xmlns="http://www.w3schools.com">
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this wend!</body>
</note>'
set @.strXML = '<?xml version="1.0" encoding="UTF-8"?>' + CONVERT(varchar(MAX
),
@.x)
SELECT @.strXML
While we're talking about encoding of XML documents I've also noticed someth
ing
strange in your example. When you create your schema collection you do this
CREATE XML SCHEMA COLLECTION MyNote AS
N'<?xml version="1.0" encoding="UTF-8"?>
....
'
go
The string literal is unicode (because of the 'N' character that preceeds it
)
but yet you specify the encoding as UTF-8 in your processing instruction. Th
e
DDL as you've written it should fail with the following error: "XML parsing:
line 1, character 38, unable to switch the encoding"
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"johnk" <johnk_hansen@.hotmail.com> wrote in message
news:1170427960.566676.200010@.s48g2000cws.googlegroups.com...
> Hello,
> In my solution I ned the xml output with including the XML header thet
> specifies the encoding.
> Here's the code:
> CREATE XML SCHEMA COLLECTION MyNote AS
> N'<?xml version="1.0" encoding="UTF-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> targetNamespace="http://www.w3schools.com" xmlns="http://
> www.w3schools.com" elementFormDefault="qualified">
> <xs:element name="note">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="to" type="xs:string"/>
> <xs:element name="from" type="xs:string"/>
> <xs:element name="heading" type="xs:string"/>
> <xs:element name="body" type="xs:string"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="HedingType" type="xs:string"/>
> </xs:schema>'
> GO
> then input a valid xml
> DECLARE @.x xml (dbo.MyNote)
> SET @.x=N'<note xmlns="http://www.w3schools.com">
> <to>Tove</to>
> <from>Jani</from>
> <heading>Reminder</heading>
> <body>Don't forget me this wend!</body>
> </note>'
> select @.x
> Here's the output:
> <note xmlns="http://www.w3schools.com">
> <to>Tove</to>
> <from>Jani</from>
> <heading>Reminder</heading>
> <body>Don't forget me this wend!</body>
> </note>
> I need the XML header that specify the encoding of the document, like
> this:
> <?xml version="1.0" encoding="UTF-8"?>
> <note xmlns="http://www.w3schools.com">
> <to>Tove</to>
> <from>Jani</from>
> <heading>Reminder</heading>
> <body>Don't forget me this wend!</body>
> </note>
> Any ideas?
>
> Thanks in advance!
> John
>|||In addition to Denis' post: You cannot get UTF-8 encoded XML data produced.
SQL Server produces UTF-16 encoded XML when you cast it to
NVARCHAR/VARBINARY, or the encoding of the code page if you cast to
VARCHAR(not recommended).
Since the NVARCHAR/VARCHAR carries the encoding on the type, we do not add
an XML declaration with an encoding, so that if you cast your string to a
different collation, the encoding does get adjusted automatically.
Now if you cast it to VARBINARY we will add the UTF-16 BOM.
If you want to produce UTF-8 encoded XML or have the XML declaration added,
you probably should use the System.XML or MSXML components on the
client/midtier side.
Best regards
Michael
"johnk" <johnk_hansen@.hotmail.com> wrote in message
news:1170439250.695037.296940@.q2g2000cwa.googlegroups.com...
> On 2 Feb, 17:46, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> I am using the query window in MS SQL Server Management Studio .
>

Missing XML header that specifies the encoding

Hello,
In my solution I ned the xml output with including the XML header thet
specifies the encoding.
Here's the code:
CREATE XML SCHEMA COLLECTION MyNote AS
N'<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.w3schools.com" xmlns="http://
www.w3schools.com" elementFormDefault="qualified">
<xs:element name="note">
<xs:complexType>
<xs:sequence>
<xs:element name="to" type="xs:string"/>
<xs:element name="from" type="xs:string"/>
<xs:element name="heading" type="xs:string"/>
<xs:element name="body" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="HedingType" type="xs:string"/>
</xs:schema>'
GO
then input a valid xml
DECLARE @.x xml (dbo.MyNote)
SET @.x=N'<note xmlns="http://www.w3schools.com">
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>'
select @.x
Here's the output:
<note xmlns="http://www.w3schools.com">
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
I need the XML header that specify the encoding of the document, like
this:
<?xml version="1.0" encoding="UTF-8"?>
<note xmlns="http://www.w3schools.com">
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
Any ideas?
Thanks in advance!
John
Well the heading is optional for UTF-8 documents. The heading's only useful
for serialised documents, how are you using the output?
Joe Fawcett (MVP - XML)
http://joe.fawcett.name
"johnk" <johnk_hansen@.hotmail.com> wrote in message
news:1170427960.566676.200010@.s48g2000cws.googlegr oups.com...
> Hello,
> In my solution I ned the xml output with including the XML header thet
> specifies the encoding.
> Here's the code:
> CREATE XML SCHEMA COLLECTION MyNote AS
> N'<?xml version="1.0" encoding="UTF-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> targetNamespace="http://www.w3schools.com" xmlns="http://
> www.w3schools.com" elementFormDefault="qualified">
> <xs:element name="note">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="to" type="xs:string"/>
> <xs:element name="from" type="xs:string"/>
> <xs:element name="heading" type="xs:string"/>
> <xs:element name="body" type="xs:string"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="HedingType" type="xs:string"/>
> </xs:schema>'
> GO
> then input a valid xml
> DECLARE @.x xml (dbo.MyNote)
> SET @.x=N'<note xmlns="http://www.w3schools.com">
> <to>Tove</to>
> <from>Jani</from>
> <heading>Reminder</heading>
> <body>Don't forget me this weekend!</body>
> </note>'
> select @.x
> Here's the output:
> <note xmlns="http://www.w3schools.com">
> <to>Tove</to>
> <from>Jani</from>
> <heading>Reminder</heading>
> <body>Don't forget me this weekend!</body>
> </note>
> I need the XML header that specify the encoding of the document, like
> this:
> <?xml version="1.0" encoding="UTF-8"?>
> <note xmlns="http://www.w3schools.com">
> <to>Tove</to>
> <from>Jani</from>
> <heading>Reminder</heading>
> <body>Don't forget me this weekend!</body>
> </note>
> Any ideas?
>
> Thanks in advance!
> John
>
|||On 2 Feb, 17:46, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> Well the heading is optional for UTF-8 documents. The heading's only useful
> for serialised documents, how are you using the output?
> --
> Joe Fawcett (MVP - XML)http://joe.fawcett.name
> "johnk" <johnk_han...@.hotmail.com> wrote in message
> news:1170427960.566676.200010@.s48g2000cws.googlegr oups.com...
>
>
>
>
>
>
>
>
> - Vis sitert tekst -
I am using the query window in MS SQL Server Management Studio .
|||John,
The processing instruction specifies the encoding when the XML is stored as
text. When you store an instance in an XML datatype, it is not stored as text
but in binary XML format. Therefore, what's the point of specifying an XML
encoding? It would have no meaning.
In your case, I would suggest you convert your XML instance to a string type and
add the processing instruction trhough string manipulation. You can do something
like this
DECLARE @.x xml (dbo.MyNote)
DECLARE @.strXML varchar(MAX)
SET @.x=N'<note xmlns="http://www.w3schools.com">
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>'
set @.strXML = '<?xml version="1.0" encoding="UTF-8"?>' + CONVERT(varchar(MAX),
@.x)
SELECT @.strXML
While we're talking about encoding of XML documents I've also noticed something
strange in your example. When you create your schema collection you do this
CREATE XML SCHEMA COLLECTION MyNote AS
N'<?xml version="1.0" encoding="UTF-8"?>
.....
'
go
The string literal is unicode (because of the 'N' character that preceeds it)
but yet you specify the encoding as UTF-8 in your processing instruction. The
DDL as you've written it should fail with the following error: "XML parsing:
line 1, character 38, unable to switch the encoding"
Denis Ruckebusch
http://blogs.msdn.com/denisruc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"johnk" <johnk_hansen@.hotmail.com> wrote in message
news:1170427960.566676.200010@.s48g2000cws.googlegr oups.com...
> Hello,
> In my solution I ned the xml output with including the XML header thet
> specifies the encoding.
> Here's the code:
> CREATE XML SCHEMA COLLECTION MyNote AS
> N'<?xml version="1.0" encoding="UTF-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> targetNamespace="http://www.w3schools.com" xmlns="http://
> www.w3schools.com" elementFormDefault="qualified">
> <xs:element name="note">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="to" type="xs:string"/>
> <xs:element name="from" type="xs:string"/>
> <xs:element name="heading" type="xs:string"/>
> <xs:element name="body" type="xs:string"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="HedingType" type="xs:string"/>
> </xs:schema>'
> GO
> then input a valid xml
> DECLARE @.x xml (dbo.MyNote)
> SET @.x=N'<note xmlns="http://www.w3schools.com">
> <to>Tove</to>
> <from>Jani</from>
> <heading>Reminder</heading>
> <body>Don't forget me this weekend!</body>
> </note>'
> select @.x
> Here's the output:
> <note xmlns="http://www.w3schools.com">
> <to>Tove</to>
> <from>Jani</from>
> <heading>Reminder</heading>
> <body>Don't forget me this weekend!</body>
> </note>
> I need the XML header that specify the encoding of the document, like
> this:
> <?xml version="1.0" encoding="UTF-8"?>
> <note xmlns="http://www.w3schools.com">
> <to>Tove</to>
> <from>Jani</from>
> <heading>Reminder</heading>
> <body>Don't forget me this weekend!</body>
> </note>
> Any ideas?
>
> Thanks in advance!
> John
>
|||In addition to Denis' post: You cannot get UTF-8 encoded XML data produced.
SQL Server produces UTF-16 encoded XML when you cast it to
NVARCHAR/VARBINARY, or the encoding of the code page if you cast to
VARCHAR(not recommended).
Since the NVARCHAR/VARCHAR carries the encoding on the type, we do not add
an XML declaration with an encoding, so that if you cast your string to a
different collation, the encoding does get adjusted automatically.
Now if you cast it to VARBINARY we will add the UTF-16 BOM.
If you want to produce UTF-8 encoded XML or have the XML declaration added,
you probably should use the System.XML or MSXML components on the
client/midtier side.
Best regards
Michael
"johnk" <johnk_hansen@.hotmail.com> wrote in message
news:1170439250.695037.296940@.q2g2000cwa.googlegro ups.com...
> On 2 Feb, 17:46, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> I am using the query window in MS SQL Server Management Studio .
>