"replace value of" and enum

I've created XML SCHEMA COLLECTION AS
<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Order" nillable="true" type="Order" />
<xs:complexType name="Order">
<xs:sequence>
<xs:element minOccurs="1" maxOccurs="1" name="Status"
type="OrderStatus" />
</xs:sequence>
</xs:complexType>
<xs:simpleType name="OrderStatus">
<xs:restriction base="xs:string">
<xs:enumeration value="New" />
<xs:enumeration value="Pending" />
<xs:enumeration value="Complete" />
</xs:restriction>
</xs:simpleType>
</xs:schema>
Now I'd like to update OrderStatus:
UPDATE Orders
SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with "Expired"')
I get:
Msg 2247, Level 16, State 1, Procedure Orders_Select
XQuery [dbo.Orders.OrderXml.modify()]: The value is of type "xs:string",
which is not a subtype of the expected type "OrderStatus".
The below does not work either:
UPDATE Orders
SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
OrderStatus("Expired")')
How do I make this work?
ThanksNever mind, ("Expired" cast as OrderStatus) does the trick.
"Chris Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:%23AHmjrHKGHA.2392@.TK2MSFTNGP09.phx.gbl...
> I've created XML SCHEMA COLLECTION AS
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema elementFormDefault="qualified"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="Order" nillable="true" type="Order" />
> <xs:complexType name="Order">
> <xs:sequence>
> <xs:element minOccurs="1" maxOccurs="1" name="Status"
> type="OrderStatus" />
> </xs:sequence>
> </xs:complexType>
> <xs:simpleType name="OrderStatus">
> <xs:restriction base="xs:string">
> <xs:enumeration value="New" />
> <xs:enumeration value="Pending" />
> <xs:enumeration value="Complete" />
> </xs:restriction>
> </xs:simpleType>
> </xs:schema>
>
> Now I'd like to update OrderStatus:
> UPDATE Orders
> SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
> "Expired"')
> I get:
> Msg 2247, Level 16, State 1, Procedure Orders_Select
> XQuery [dbo.Orders.OrderXml.modify()]: The value is of type "xs:string",
> which is not a subtype of the expected type "OrderStatus".
> The below does not work either:
> UPDATE Orders
> SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
> OrderStatus("Expired")')
>
> How do I make this work?
>
> Thanks
>
>
>

"replace value of" and enum

I've created XML SCHEMA COLLECTION AS
<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Order" nillable="true" type="Order" />
<xs:complexType name="Order">
<xs:sequence>
<xs:element minOccurs="1" maxOccurs="1" name="Status"
type="OrderStatus" />
</xs:sequence>
</xs:complexType>
<xs:simpleType name="OrderStatus">
<xs:restriction base="xs:string">
<xs:enumeration value="New" />
<xs:enumeration value="Pending" />
<xs:enumeration value="Complete" />
</xs:restriction>
</xs:simpleType>
</xs:schema>
Now I'd like to update OrderStatus:
UPDATE Orders
SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with "Expired"')
I get:
Msg 2247, Level 16, State 1, Procedure Orders_Select
XQuery [dbo.Orders.OrderXml.modify()]: The value is of type "xs:string",
which is not a subtype of the expected type "OrderStatus".
The below does not work either:
UPDATE Orders
SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
OrderStatus("Expired")')
How do I make this work?
Thanks
Never mind, ("Expired" cast as OrderStatus) does the trick.
"Chris Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:%23AHmjrHKGHA.2392@.TK2MSFTNGP09.phx.gbl...
> I've created XML SCHEMA COLLECTION AS
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema elementFormDefault="qualified"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="Order" nillable="true" type="Order" />
> <xs:complexType name="Order">
> <xs:sequence>
> <xs:element minOccurs="1" maxOccurs="1" name="Status"
> type="OrderStatus" />
> </xs:sequence>
> </xs:complexType>
> <xs:simpleType name="OrderStatus">
> <xs:restriction base="xs:string">
> <xs:enumeration value="New" />
> <xs:enumeration value="Pending" />
> <xs:enumeration value="Complete" />
> </xs:restriction>
> </xs:simpleType>
> </xs:schema>
>
> Now I'd like to update OrderStatus:
> UPDATE Orders
> SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
> "Expired"')
> I get:
> Msg 2247, Level 16, State 1, Procedure Orders_Select
> XQuery [dbo.Orders.OrderXml.modify()]: The value is of type "xs:string",
> which is not a subtype of the expected type "OrderStatus".
> The below does not work either:
> UPDATE Orders
> SET OrderXml.modify('replace value of (/Order[1]/Status[1]) with
> OrderStatus("Expired")')
>
> How do I make this work?
>
> Thanks
>
>
>

"Invalid character in the given encoding". XML bulk load, SQLE

Hi Martin,
I confirm that the problem is the presence of the character "è", even if it
is in the comment. How can I solve the problem?
I have put the line
<?xml version="1.0" encoding="utf-8"?>
for the encoding. What else should I do?
"Martin Honnen" wrote:

> Tatopitta wrote:
>
> The error usually means that a byte sequence has been encountered that
> does not map to a character in the specified encoding in the XML
> declaration.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
>Tatopitta wrote:

> I confirm that the problem is the presence of the character "è", even if
it
> is in the comment. How can I solve the problem?
> I have put the line
> <?xml version="1.0" encoding="utf-8"?>
> for the encoding. What else should I do?
Make sure the document is really UTF-8 encoded, with UTF-8 the character
"è" needs to be encoded with the two byte sequence C3 A8.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

"for xml raw, element" output format

This query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml raw('theme'), elements
gives me this output:
<theme>
<themekey>airborne geophysical survey</themekey>
</theme>
<theme>
<themekey>airborne geophysics</themekey>
</theme>
<theme>
<themekey>diamonds</themekey>
</theme>
<theme>
<themekey>drilling</themekey>
</theme>
<theme>
<themekey>geochemical survey</themekey>
</theme>
<theme>
<themekey>geology</themekey>
</theme>
How do I get the format to look as follows:
<theme>
<themekey>airborne geophysical survey</themekey>
<themekey>airborne geophysics</themekey>
<themekey>diamonds</themekey>
<themekey>drilling</themekey>
<themekey>geochemical survey</themekey>
<themekey>geology</themekey>
</theme>
Thanks,
Lee AnneYou did not mention the version of SQL Server (is it 2000 or 2005).
In SQL Server 2005 you can query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml path(''), root('theme')
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne|||Ok, that's SQL Server 2005 :-) I should look at given query more carefully
:-)
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne|||Thank you, that was exactly what I needed!!!
Lee Anne
"Pawel Potasinski" wrote:

> Ok, that's SQL Server 2005 :-) I should look at given query more carefully
> :-)
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa3 w
> wiadomo?ci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
>
>

"for xml raw, element" output format

This query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml raw('theme'), elements
gives me this output:
<theme>
<themekey>airborne geophysical survey</themekey>
</theme>
<theme>
<themekey>airborne geophysics</themekey>
</theme>
<theme>
<themekey>diamonds</themekey>
</theme>
<theme>
<themekey>drilling</themekey>
</theme>
<theme>
<themekey>geochemical survey</themekey>
</theme>
<theme>
<themekey>geology</themekey>
</theme>
How do I get the format to look as follows:
<theme>
<themekey>airborne geophysical survey</themekey>
<themekey>airborne geophysics</themekey>
<themekey>diamonds</themekey>
<themekey>drilling</themekey>
<themekey>geochemical survey</themekey>
<themekey>geology</themekey>
</theme>
Thanks,
Lee Anne
You did not mention the version of SQL Server (is it 2000 or 2005).
In SQL Server 2005 you can query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml path(''), root('theme')
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne
|||Ok, that's SQL Server 2005 :-) I should look at given query more carefully
:-)
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne
|||Thank you, that was exactly what I needed!!!
Lee Anne
"Pawel Potasinski" wrote:

> Ok, that's SQL Server 2005 :-) I should look at given query more carefully
> :-)
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa3 w
> wiadomo?ci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
>
>

"FOR XML Explicit" why poor performance, and any "quick wins" ?

Just started a new job and need a quick fix if possible.
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tweeks I can make right now will be of benefit.
Thanks
Steve ...
Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tweeks I can make right now will be of benefit.
> Thanks
> Steve ...
|||Thanks for reply,
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:

> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, you
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhead
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>
|||For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
[vbcol=seagreen]
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event because
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that these
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:

"FOR XML Explicit" why poor performance, and any "quick wins" ?

Just started a new job and need a quick fix if possible.
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tws I can make right now will be of benefit.
Thanks
Steve ...Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tws I can make right now will be of benefit.
> Thanks
> Steve ...

"FOR XML Explicit" why poor performance, and any "quick wins"

Thanks for reply,
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:

> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, yo
u
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhe
ad
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in messa
ge
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event becau
se
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that the
se
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables
in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:
>