(Cancel this) Can’t paste into XML field in Management Studio.

Hi Folks,

It looks like Management Studio prevents pasting XML directly into a field of the type xml. Sometimes, it might be best to stay with varchar(max).

Original post below: >>

I want to paste some XML into an SQL Server 2005 xml type field, but the null is grayed out and paste simply does not work. What is going on?

Thanks,

Rob

Rob:

I see the same behavior. It appears to me that the COPY function is not active for the XML field and that you cannot directly modify it. You can copy from the XML field, but you cannot paste into it. I suggest that you use an XML query to update your field?

"XML Source" xsd relative path

Hi all,

Is it possible in the XML Source to specify a relative path for the xsd file? If not, do you have any idea why?

Best regards,
pcSSIS doesn't support relative paths anywhere that I'm aware of. As to why, there is no assumption that a package will ever exist in the filesystem and thus have a root path from which to base a relative part. Packages can be created programmatically and executed, or stored in SQL Server.

There are a couple techniques to build fake relative paths from dynamically defined roots such as the USERPROFILE environment variable that have been discussed in other threads.

|||Hi JayH,

If the package is executed in the SQL Server where should the xsd file be stored? I have a Web Service Task for extracting data from a Web service . It writes the web service output in a xml file and after that this xml file is used as a source for a XML Source. Finally the data from the web service is inserted in a SQL Server database. Where in the database then, not in the file system, should I store the xml file and the xsd for it? And actually this relative path question came out because the SSIS packages we develop are under source control and if they do not support relative paths it is very difficult for two people to work on the same package under source control.

Best regards,
pc|||

pc_83 wrote:

Hi JayH,

If the package is executed in the SQL Server where should the xsd file be stored? I have a Web Service Task for extracting data from a Web service . It writes the web service output in a xml file and after that this xml file is used as a source for a XML Source. Finally the data from the web service is inserted in a SQL Server database. Where in the database then, not in the file system, should I store the xml file and the xsd for it? And actually this relative path question came out because the SSIS packages we develop are under source control and if they do not support relative paths it is very difficult for two people to work on the same package under source control.

Best regards,
pc

Not sure I understand your question, but I think your only options are to have the XSD on the disk or inside the XML. The XML can either be a file or a variable. You could read the XSD from the database and insert that into the XML you get from the web service if you want to avoid the disk.

I understand the Source Control scenario very well. In my case, we decided that all package paths would be relative to each user's profile. Each developer was required make their working folder the same location under "My Documents". This was also handy since multiple users could log into the big development server and work on the same solution without interfering with each other's child packages, raw files, etc. All paths were constructed dynamically at run-time using expression-based variables and the USERPROFILE environment variable that was read with a package configuration.

|||Thank you for the detailed answer. I thought it would be easiest, bit obviously I do not have much choice.

Best regards,
pc

"Too low memory" and XML in cache

Hi,

I've read that the parser can use 1/8th of the available memory for the xml-cache, but I've run into this limit much sooner than I'd expect.

I'm not using "sp_xml_preparedocument", but rather operating on columns with XML as datatype. This means I don't have the handle to the document and can't use "sp_xml_removedocument" to free the memory.

It's a simple application written in C#, so should it be sufficient to "reset" the sql-connection or how should I do it?

*I've tried to keep the post as simple as I could, but I might have simplified it too much?*

What is you application doing with XML, what level of concurrency?

When I investigated the 1/8 question I couldn't get any forumla on any machine to get to this number. THe one thing I did find was that it appeared to be related to the amount of memory available at startup of sql server. If running on a user machine you are likely to have lots of other apps running, VS, Managment Studio, help etc. These just take chunks out of the available memory for SQL.

I ahev no experience of the XML data type memory requirements in 2005 sorry.

|||

Hi,

My application does nothing more than look-ups and it does it all on one connection. As far as I can see, closing the connection should free the cache though.

My "problem" is that Im not using "sp_xml_preparedocument" and thus not getting the handle for the document.

Perhaps there is a way to see the "cache-status"?

I got the 1/8 from the reference regarding the stored procedure "sp_xml_preparedocument". http://msdn2.microsoft.com/en-us/library/ms187367(SQL.90).aspx

"A parsed document is stored in the internal cache of SQL Server 2005. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory"

|||Can you post the error you are getting, when you are getting it and as much of the code as possible.|||

Basically I either get one of the following messages:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

or

XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents. (when doing SqlDataReader reader = cmd.ExecuteReader(); )

I'll do a few more tests to see if I can narrow it down a little, because I noticed that the application and sql-process didn't increase their memory usage, but the pagefile-usage kept increasing.

The program is printing some invoices, so the printing-part might influence it.

*heads back to his experiement*

|||

What SQL are you executing? Without this we can't really work out what might be the problem.

When you get the timeout, what processes are using the cpu?

|||

I'll clean/write it up, as it is rather embedded right now. I hope to make it clearer and without as much "clutter"-code.

In reality it is many select-statements on a table with a column with the xml-type. I'll eloborate later when I get it "readable"

Im running the application in debug-mode from Visual C# 2005.

|||

*slaps his forehead*

I think I found my mistake. The sp_xml_preparedocument did indeed get called (was hidden away in a stored procedure)

I'll run a test and see if it works.

-- *some variable names are in danish*

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[FakturaSP] AS

CREATE TABLE #philTemp

(prim int identity,

connectionID int)

--Fill #philTemp with the ConnectionIDs

INSERT INTO #philTemp SELECT ConnectionID FROM Connections

WHERE XmlData.exist('/Bat2005.Connection[ConnectionType = "FakturaHoved_OrdreHoved"]') = 1

--SELECT * FROM #philTemp

declare @.counter int

declare @.maxCount int

DECLARE @.doc xml

DECLARE @.idoc int

set @.counter = 0

SELECT @.maxCount = COUNT(*) FROM #philTemp

--Create table with the ordrehoveder that has been invoiced

CREATE TABLE #faktureredeOrdrehoveder

(prim int identity,

ordrehovedID int)

while @.counter < @.maxCount begin

set @.counter = @.counter + 1

SELECT @.doc = XmlData FROM Connections

WHERE ConnectionID = (SELECT ConnectionID FROM #philTemp WHERE prim = @.counter)

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

INSERT INTO #faktureredeOrdrehoveder SELECT *

FROM OPENXML (@.idoc, '/Bat2005.Connection',2)

WITH (IDofChild int)

EXEC sp_xml_removedocument @.idoc

end

--SELECT * FROM #faktureredeOrdrehoveder

--

CREATE TABLE #alleOrdrehoveder

(prim int identity,

ordrehovedID int)

--Fill #philTemp with the ConnectionIDs

INSERT INTO #alleOrdrehoveder SELECT ComponentID FROM Components

WHERE XmlData.exist('/BAT.Templates.DanNET.Ordrehoved') = 1

--SELECT * FROM #alleOrdrehoveder ORDER BY ordrehovedID asc;

SELECT #alleOrdrehoveder.ordrehovedID

FROM #alleOrdrehoveder LEFT JOIN #faktureredeOrdrehoveder ON #alleOrdrehoveder.ordrehovedID = #faktureredeOrdrehoveder.ordrehovedID

WHERE (((#faktureredeOrdrehoveder.ordrehovedID) Is Null)) ORDER BY ordrehovedID asc;

--Clean up

DROP TABLE #alleOrdrehoveder

DROP TABLE #faktureredeOrdrehoveder

DROP TABLE #philTemp

|||

A couple of comments.

Always create temporary tables at the very start, avoids the possibility of recompiles ( i know 2005 is better, but this is good practice)

Have you tried using the nodes method, have a look at the following link in BOL

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7267fe1b-2e34-4213-8bbf-1c953822446c.htm

Avoids the use of openXML.

|||

Thanks for the pointers, I'll have a look at the right away.

As for the stored procedure, it's a procedure I made to find the orders that haven't been invoiced yet. I didn't really plan to keep it (but it's doing its job) and I didn't expect to find the error here. (as you could see from the code I'm not really used to making stored procedures)

I just ran the test and it completed, although the pagefile is still increasing, but Im guessing that's due to another mistake somewhere else :-)

/Philip

|||

"Basically I either get one of the following messages:Timeout expired. The timeout period elapsed .."

Your second error "low memory" most probably is caused by the time out error. The reason is when time out happened, you SP has no chance to run the procedure sp_xml_removedocument.

One trick you can do when this happends is to call sp_xml_removedocument in a loop.

DECLARE @.Loop int

SET @.Loop=1

WHILE @.Loop<1000000

BEGIN

exec sp_xml_removedocument @.Loop

SET @.Loop=@.Loop+1

END

It may print lots of errors if @.Loop is not a valid hanlde of xml. But if it's, it will be removed.

For how to avoid memory leaking in this situation, please reference OPENXML and memory leak

|||A good point about xml in a transaction, thankssql

"Too low memory" and XML in cache

Hi,

I've read that the parser can use 1/8th of the available memory for the xml-cache, but I've run into this limit much sooner than I'd expect.

I'm not using "sp_xml_preparedocument", but rather operating on columns with XML as datatype. This means I don't have the handle to the document and can't use "sp_xml_removedocument" to free the memory.

It's a simple application written in C#, so should it be sufficient to "reset" the sql-connection or how should I do it?

*I've tried to keep the post as simple as I could, but I might have simplified it too much?*

What is you application doing with XML, what level of concurrency?

When I investigated the 1/8 question I couldn't get any forumla on any machine to get to this number. THe one thing I did find was that it appeared to be related to the amount of memory available at startup of sql server. If running on a user machine you are likely to have lots of other apps running, VS, Managment Studio, help etc. These just take chunks out of the available memory for SQL.

I ahev no experience of the XML data type memory requirements in 2005 sorry.

|||

Hi,

My application does nothing more than look-ups and it does it all on one connection. As far as I can see, closing the connection should free the cache though.

My "problem" is that Im not using "sp_xml_preparedocument" and thus not getting the handle for the document.

Perhaps there is a way to see the "cache-status"?

I got the 1/8 from the reference regarding the stored procedure "sp_xml_preparedocument". http://msdn2.microsoft.com/en-us/library/ms187367(SQL.90).aspx

"A parsed document is stored in the internal cache of SQL Server 2005. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory"

|||Can you post the error you are getting, when you are getting it and as much of the code as possible.|||

Basically I either get one of the following messages:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

or

XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents. (when doing SqlDataReader reader = cmd.ExecuteReader(); )

I'll do a few more tests to see if I can narrow it down a little, because I noticed that the application and sql-process didn't increase their memory usage, but the pagefile-usage kept increasing.

The program is printing some invoices, so the printing-part might influence it.

*heads back to his experiement*

|||

What SQL are you executing? Without this we can't really work out what might be the problem.

When you get the timeout, what processes are using the cpu?

|||

I'll clean/write it up, as it is rather embedded right now. I hope to make it clearer and without as much "clutter"-code.

In reality it is many select-statements on a table with a column with the xml-type. I'll eloborate later when I get it "readable"

Im running the application in debug-mode from Visual C# 2005.

|||

*slaps his forehead*

I think I found my mistake. The sp_xml_preparedocument did indeed get called (was hidden away in a stored procedure)

I'll run a test and see if it works.

-- *some variable names are in danish*

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[FakturaSP] AS

CREATE TABLE #philTemp

(prim int identity,

connectionID int)

--Fill #philTemp with the ConnectionIDs

INSERT INTO #philTemp SELECT ConnectionID FROM Connections

WHERE XmlData.exist('/Bat2005.Connection[ConnectionType = "FakturaHoved_OrdreHoved"]') = 1

--SELECT * FROM #philTemp

declare @.counter int

declare @.maxCount int

DECLARE @.doc xml

DECLARE @.idoc int

set @.counter = 0

SELECT @.maxCount = COUNT(*) FROM #philTemp

--Create table with the ordrehoveder that has been invoiced

CREATE TABLE #faktureredeOrdrehoveder

(prim int identity,

ordrehovedID int)

while @.counter < @.maxCount begin

set @.counter = @.counter + 1

SELECT @.doc = XmlData FROM Connections

WHERE ConnectionID = (SELECT ConnectionID FROM #philTemp WHERE prim = @.counter)

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

INSERT INTO #faktureredeOrdrehoveder SELECT *

FROM OPENXML (@.idoc, '/Bat2005.Connection',2)

WITH (IDofChild int)

EXEC sp_xml_removedocument @.idoc

end

--SELECT * FROM #faktureredeOrdrehoveder

--

CREATE TABLE #alleOrdrehoveder

(prim int identity,

ordrehovedID int)

--Fill #philTemp with the ConnectionIDs

INSERT INTO #alleOrdrehoveder SELECT ComponentID FROM Components

WHERE XmlData.exist('/BAT.Templates.DanNET.Ordrehoved') = 1

--SELECT * FROM #alleOrdrehoveder ORDER BY ordrehovedID asc;

SELECT #alleOrdrehoveder.ordrehovedID

FROM #alleOrdrehoveder LEFT JOIN #faktureredeOrdrehoveder ON #alleOrdrehoveder.ordrehovedID = #faktureredeOrdrehoveder.ordrehovedID

WHERE (((#faktureredeOrdrehoveder.ordrehovedID) Is Null)) ORDER BY ordrehovedID asc;

--Clean up

DROP TABLE #alleOrdrehoveder

DROP TABLE #faktureredeOrdrehoveder

DROP TABLE #philTemp

|||

A couple of comments.

Always create temporary tables at the very start, avoids the possibility of recompiles ( i know 2005 is better, but this is good practice)

Have you tried using the nodes method, have a look at the following link in BOL

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7267fe1b-2e34-4213-8bbf-1c953822446c.htm

Avoids the use of openXML.

|||

Thanks for the pointers, I'll have a look at the right away.

As for the stored procedure, it's a procedure I made to find the orders that haven't been invoiced yet. I didn't really plan to keep it (but it's doing its job) and I didn't expect to find the error here. (as you could see from the code I'm not really used to making stored procedures)

I just ran the test and it completed, although the pagefile is still increasing, but Im guessing that's due to another mistake somewhere else :-)

/Philip

|||

"Basically I either get one of the following messages:Timeout expired. The timeout period elapsed .."

Your second error "low memory" most probably is caused by the time out error. The reason is when time out happened, you SP has no chance to run the procedure sp_xml_removedocument.

One trick you can do when this happends is to call sp_xml_removedocument in a loop.

DECLARE @.Loop int

SET @.Loop=1

WHILE @.Loop<1000000

BEGIN

exec sp_xml_removedocument @.Loop

SET @.Loop=@.Loop+1

END

It may print lots of errors if @.Loop is not a valid hanlde of xml. But if it's, it will be removed.

For how to avoid memory leaking in this situation, please reference OPENXML and memory leak

|||A good point about xml in a transaction, thanks

"order by" in xquery

Given the following XML data in SQL Server (where each Employee is a separate rowset), is there a way to order by ID using XQuery?

Row 1<Employee><ID>3</ID></Employee>
Rpw 2<Employee><ID>1</ID></Employee>
Row 3<Employee><ID>2</ID></Employee>

Ex:

SELECT Employee.query('
for $emp in /Employee
order by $emp/ID
')

If you want to order multiple rows containing xml data you need to use SQL order by. XQuery order by is scoped to only one XML instance.

Assuming that table t has the Employee column, you can try something like this:

SELECT Employee

FROM t

ORDER BY Employee.value('(/Employee/ID)[1]', 'int')

|||Adrian, you are the man! Thanks!!

"FOR XML EXPLICIT" query Works on SQL 2000 but same does not...

We are using a stored procedure which uses FOR XML EXPLICIT and it works fine with SQL Server 2000 but doesnt work with SQL Server 2005. Can anyone help me out in understanding the reason behind such a behavior and any possible solution. Please find the details of the problem below:

The procedure runs fine in SQL 2000 the input xml and gives us the correct XML:

SELECT

1 AS TAG,
NULL AS PARENT,
[TEST:mailboxaddress] AS [mailbox!1!mailbox-name!element],
[TEST:status] AS [mailbox!1!mailbox-status!element],
NULL AS [user!2!title!element],
NULL AS [user!2!firstname!xml],
NULL AS [user!2!lastname!xml],
NULL AS [user!2!login!element],
tUser.id AS [user!2!userID!element]

FROM TbUser AS tUser

INNER JOIN
OPENXML (@.idoc, '//TEST:mbox',2)
WITH ([TEST:mailboxaddress] NVARCHAR(100),
[TEST:status] NVARCHAR(20),
[TEST:userid] UNIQUEIDENTIFIER) AS mailbox
ON mailbox.[TEST:userid] = tUser.id

UNION ALL
SELECT 2 AS TAG,
1 AS PARENT,
[TEST:mailboxaddress] AS [mailbox!1!mailbox-name!element],
[TEST:status] AS [mailbox!1!mailbox-status!element],
tUser.title_lookup_id AS [user!2!title!element],
tUser.firstname AS [user!2!firstname!xml],
tUser.lastname AS [user!2!lastname!xml],
tUser.login_name AS [user!2!login!element],
tUser.id AS [user!2!userID!element]
FROM TbUser AS tUser
INNER JOIN
OPENXML (@.idoc, '//TEST:mbox',2)
WITH ([TEST:mailboxaddress] NVARCHAR(100),
[TEST:status] NVARCHAR(20),
[TEST:userid] UNIQUEIDENTIFIER) AS mailbox
ON mailbox.[TEST:userid] = tUser.id
order by [mailbox!1!mailbox-name!element], [user!2!userID!element]
FOR XML EXPLICIT

This runs perfectly fine in 2000 but in the 2005 the XML is not correctly formed .. i am not able to figure out why this issue is occuring. In 2005 tt gives me all the tags with the expected values but the sequence is not correct.

Thanks,

Gaurav

Can you give me a more specific case to reproduce your problem so I might help you to find the reason/solution?|||You'll need to give some sample data, but have you tried just adding ORDER BY to the queries. The data will be returned in no particular order if you don't specify ORDER BY, so if it is "correct" on 2000 but "incorrect" on 2005 that is purely by chance.|||

You asked the same question in sql server central. I answered your question there a week ago and don't know solved your problem or not. The answer is to change "tUser.id AS [user!2!userID!element]" to "NULL AS [user!2!userID!element]" in the first SELECT statement in the UNION.

' in the output of XML Explicit

Hi,
I have got a piece of XML genereated from XML Explicit like below which
should display an apostrophe instead of '
<ITEM_DESCRIIPTION id=1 desc1="Super Wine 12's"/>
I tried to add xml directive but seems it only works for element but not
attribute.
Could somebody help me out?
Thanks very much!
Cloudcloudx wrote:
> Hi,
> I have got a piece of XML genereated from XML Explicit like below which
> should display an apostrophe instead of '
> <ITEM_DESCRIIPTION id=1 desc1="Super Wine 12's"/>
When processed, this is exactly equivalent to a unidirectional
(typewriter-style) apostrophe. What's the problem? (Apart from the fact
that your example isn't well-formed: it needs quotes in the id="1".)
///Peter|||
"Peter Flynn" wrote:

> cloudx wrote:
> When processed, this is exactly equivalent to a unidirectional
> (typewriter-style) apostrophe. What's the problem? (Apart from the fact
> that your example isn't well-formed: it needs quotes in the id="1".)
> ///Peter
>|||Hi Peter,
Thanks for your reply. To clearly explain the issue I am having, here is a
piece of SQL code that returns a string of XML.
Select 1 as Tag,
NULL as Parent,
NULL as [PRODUCT!1],
NULL as [ITEM_DESCRIPTION!2!id],
NULL as [ITEM_DESCRIPTION!2!desc]
union
select 2,
1,
'PRODUCT'
, null
, null
union
select 2,
1,
null,
1,
'super wine 12''s'
for xml explicit
The output is:
<PRODUCT><ITEM_DESCRIPTION id="1" desc="super wine
12's"/><ITEM_DESCRIPTION/></PRODUCT>
I would like to get rid of ' and show the original '.
Thanks!
Cloud
"Peter Flynn" wrote:

> cloudx wrote:
> When processed, this is exactly equivalent to a unidirectional
> (typewriter-style) apostrophe. What's the problem? (Apart from the fact
> that your example isn't well-formed: it needs quotes in the id="1".)
> ///Peter
>|||You can use the !xml directive
Change
NULL as [ITEM_DESCRIPTION!2!desc]
to
NULL as [ITEM_DESCRIPTION!2!desc!xml]|||Thanks for your reply. With xml directive it only gives me desc element but
I
want desc attribute.
To further explain my issue:
I understand there is no issue of displaying the XML with ' in
iexploere as it will be converted to real apostrophe, however the real issue
is that if I look at the output in say Textpad the ' is there and when
I
try to validate the output against the schema, the ' occupies 6
characters and cause the maximum length exceed.
e.g. the desc is defined as 15 characters in the schema which just fits
"super wine 12's", however it will fail on "super wine 12's".
That's the issue!
Please help!
Thanks!
Cloud
"markc600@.hotmail.com" wrote:

> You can use the !xml directive
> Change
> NULL as [ITEM_DESCRIPTION!2!desc]
> to
> NULL as [ITEM_DESCRIPTION!2!desc!xml]
>|||AFAIK, SQL Server 2000 won't let you generate unencoded
attributes (only unencoded elements using the !xml directive).|||The validation error looks like a bug in the validator.
XML requires/allows encoding apostrophe as '. The information set value
is exactly the same as the apostrophe itself (and the validator should
accept it as such).
Having said that, SQL Server 2005 is actually returning the apostrophe as is
(and not entitized) for your query below. So you could upgrade from 2000 to
2005 ;-)
Best regards
Michael
"cloudx" <cloudx@.discussions.microsoft.com> wrote in message
news:8D8E014E-4DAB-4BAF-9D21-4C3413154B65@.microsoft.com...
> Thanks for your reply. With xml directive it only gives me desc element
> but I
> want desc attribute.
> To further explain my issue:
> I understand there is no issue of displaying the XML with ' in
> iexploere as it will be converted to real apostrophe, however the real
> issue
> is that if I look at the output in say Textpad the ' is there and
> when I
> try to validate the output against the schema, the ' occupies 6
> characters and cause the maximum length exceed.
> e.g. the desc is defined as 15 characters in the schema which just fits
> "super wine 12's", however it will fail on "super wine 12's".
> That's the issue!
> Please help!
> Thanks!
> Cloud
> "markc600@.hotmail.com" wrote:
>|||cloudx wrote:
> Thanks for your reply. With xml directive it only gives me desc element bu
t I
> want desc attribute.
> To further explain my issue:
> I understand there is no issue of displaying the XML with ' in
> iexploere as it will be converted to real apostrophe, however the real iss
ue
> is that if I look at the output in say Textpad the ' is there and whe
n I
> try to validate the output against the schema, the ' occupies 6
> characters and cause the maximum length exceed.
> e.g. the desc is defined as 15 characters in the schema which just fits
> "super wine 12's", however it will fail on "super wine 12's".
> That's the issue!
You've got some broken software. As I said, the ' is exactly
equivalent to "'", and any XML software which treats it otherwise
is almost certainly wrong. A max length should be applied *after*
entity resolution, not before.
///Peter
--
XML FAQ: http://xml.silmaril.ie/

' in the output of XML Explicit

Hi,
I have got a piece of XML genereated from XML Explicit like below which
should display an apostrophe instead of '
<ITEM_DESCRIIPTION id=1 desc1="Super Wine 12's"/>
I tried to add xml directive but seems it only works for element but not
attribute.
Could somebody help me out?
Thanks very much!
Cloud
cloudx wrote:
> Hi,
> I have got a piece of XML genereated from XML Explicit like below which
> should display an apostrophe instead of '
> <ITEM_DESCRIIPTION id=1 desc1="Super Wine 12's"/>
When processed, this is exactly equivalent to a unidirectional
(typewriter-style) apostrophe. What's the problem? (Apart from the fact
that your example isn't well-formed: it needs quotes in the id="1".)
///Peter
|||"Peter Flynn" wrote:

> cloudx wrote:
> When processed, this is exactly equivalent to a unidirectional
> (typewriter-style) apostrophe. What's the problem? (Apart from the fact
> that your example isn't well-formed: it needs quotes in the id="1".)
> ///Peter
>
|||Hi Peter,
Thanks for your reply. To clearly explain the issue I am having, here is a
piece of SQL code that returns a string of XML.
Select 1as Tag,
NULLas Parent,
NULLas [PRODUCT!1],
NULLas [ITEM_DESCRIPTION!2!id],
NULLas [ITEM_DESCRIPTION!2!desc]
union
select 2,
1,
'PRODUCT'
, null
, null
union
select 2,
1,
null,
1,
'super wine 12''s'
for xml explicit
The output is:
<PRODUCT><ITEM_DESCRIPTION id="1" desc="super wine
12's"/><ITEM_DESCRIPTION/></PRODUCT>
I would like to get rid of ' and show the original '.
Thanks!
Cloud
"Peter Flynn" wrote:

> cloudx wrote:
> When processed, this is exactly equivalent to a unidirectional
> (typewriter-style) apostrophe. What's the problem? (Apart from the fact
> that your example isn't well-formed: it needs quotes in the id="1".)
> ///Peter
>
|||You can use the !xml directive
Change
NULL as [ITEM_DESCRIPTION!2!desc]
to
NULL as [ITEM_DESCRIPTION!2!desc!xml]
|||Thanks for your reply. With xml directive it only gives me desc element but I
want desc attribute.
To further explain my issue:
I understand there is no issue of displaying the XML with ' in
iexploere as it will be converted to real apostrophe, however the real issue
is that if I look at the output in say Textpad the ' is there and when I
try to validate the output against the schema, the ' occupies 6
characters and cause the maximum length exceed.
e.g. the desc is defined as 15 characters in the schema which just fits
"super wine 12's", however it will fail on "super wine 12's".
That's the issue!
Please help!
Thanks!
Cloud
"markc600@.hotmail.com" wrote:

> You can use the !xml directive
> Change
> NULL as [ITEM_DESCRIPTION!2!desc]
> to
> NULL as [ITEM_DESCRIPTION!2!desc!xml]
>
|||AFAIK, SQL Server 2000 won't let you generate unencoded
attributes (only unencoded elements using the !xml directive).
|||The validation error looks like a bug in the validator.
XML requires/allows encoding apostrophe as '. The information set value
is exactly the same as the apostrophe itself (and the validator should
accept it as such).
Having said that, SQL Server 2005 is actually returning the apostrophe as is
(and not entitized) for your query below. So you could upgrade from 2000 to
2005 ;-)
Best regards
Michael
"cloudx" <cloudx@.discussions.microsoft.com> wrote in message
news:8D8E014E-4DAB-4BAF-9D21-4C3413154B65@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply. With xml directive it only gives me desc element
> but I
> want desc attribute.
> To further explain my issue:
> I understand there is no issue of displaying the XML with ' in
> iexploere as it will be converted to real apostrophe, however the real
> issue
> is that if I look at the output in say Textpad the ' is there and
> when I
> try to validate the output against the schema, the ' occupies 6
> characters and cause the maximum length exceed.
> e.g. the desc is defined as 15 characters in the schema which just fits
> "super wine 12's", however it will fail on "super wine 12's".
> That's the issue!
> Please help!
> Thanks!
> Cloud
> "markc600@.hotmail.com" wrote:
|||cloudx wrote:
> Thanks for your reply. With xml directive it only gives me desc element but I
> want desc attribute.
> To further explain my issue:
> I understand there is no issue of displaying the XML with ' in
> iexploere as it will be converted to real apostrophe, however the real issue
> is that if I look at the output in say Textpad the ' is there and when I
> try to validate the output against the schema, the ' occupies 6
> characters and cause the maximum length exceed.
> e.g. the desc is defined as 15 characters in the schema which just fits
> "super wine 12's", however it will fail on "super wine 12's".
> That's the issue!
You've got some broken software. As I said, the ' is exactly
equivalent to "'", and any XML software which treats it otherwise
is almost certainly wrong. A max length should be applied *after*
entity resolution, not before.
///Peter
XML FAQ: http://xml.silmaril.ie/

'concatenating' several columns together for Insert into an XML column

Hi,

I am inserting a row into a Table. One of the columns in this table is of XML datatype.

I am using a Select statement to provide the values that will be inserted into the row. However, I would like to combine some of these values and parse them into XML for insertion into the XML column.

An example might help:

The Table to insert into has the following columns:

Table name: myTable

ID int

Data1 string

Data2 string

Data3 string

DataXml xml

I am inserting into this table using the following:

Code Snippet

insertinto myTable

(Data1, Data2, Data3, DataXml)

select Data1, Data2, Data3,

(select Data4, Data5, Data6)from myOtherTable as inside

where inside.ID = outside.ID forxmlraw)

from myOtherTable as outside

This works but it strikes me as inefficient. There is an additional lookup for each row which seems unnecessary as we are already at that row of the same table.

If it was a simple concatenation, I would do something like 'Data4 + Data5 + Data6', and it seems this is not much different except that instead of concatenation everything is being wrapped in Xml.

Does anyone have any ideas of a better way to do this?

Any help much appreciated.

Holf:

I am definitely not an expert at XML. I too am learning. This is also pretty much the way that I do it. I am also interested in seeing if Martin has a better way of doing this. To me, it looks like you are doing it right.

Kent

|||

Kent,

Thanks for the response. Well, I've checked the query plan generated and I can confirm that there is defintely some nested looping going on. I was wondering if the optimizer would realize what is happening and do some shortcuts.

Given this, I'm thinking it is going to be more efficient to concatenate the XML bits in, e.g. '<row Data4="' + Data4 + '" />' etc.

I don't want to do this because to manipulate XML I'd like to use XML tools, but I'll happily concatenate if it works out to be faster.

Thanks again for the reply. I'm learning XML too, and it does seem as though there's a lot to learn just now!

Holf

|||Be aware that FOR XML will do necessary escaping (e.g. a value like "foo & bar" will be escaped as "foo &amp; bar") while SQL string concatenation will not do any escaping that XML requires.|||Thank you, Martin, I had forgotten about that particular side effect; I have experienced pain from this particular problem a couple of times.|||

Ah yes, that is a very good point.

Well, I may write a function to which I can pass my XML elements and which will respond with some nicely crafted XML. I am considering compiling and importing a .NET assembly specifically for this task, as in .NET there are some nice tools for building up valid XML from raw elements, and these tools take care of escaping invalid characters.

I know this sounds like overkill but I still think it will be more efficient than the subquery approach above. Of course, I will be testing to find out.

Better check that my hosting provider allows upload of .NET assemblies to SQL Server...

Thanks Martin and Kent for your thoughts on this.

|||

Well, I've checked with my hosting provider and they do not allow upload of .NET assemblies. This is not surprising really, given it is a shared database and with the rights to upload assemblies, users could compromise the entire database.

So, I've written a very simple function to escape the necessary characters (of which there are not many from what I read):

Code Snippet

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERFUNCTION [dbo].[fn_EscapeForXml]

(

@.stringToEscape asnvarchar(256)

)

RETURNSnvarchar(256)

AS

BEGIN

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'&','&amp;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'<','&lt;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'>','&gt;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'''','&apos;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'"','&quot;')

RETURN(@.stringToEscape)

END

Note that the '&' has to be escaped first, or otherwise the '&'s resulting from the other replace operations are themselves replaced.

When I am concatenating my strings to make an XML document, I pass anything which I know may have invalid characters through this function first.

I have had some XML that I have treated in this way returned to a .NET environment. When the XML is deserialized these characters are resolved as they should be into their original form, so it all seems to be working.