Showing posts with label xml. Show all posts
Showing posts with label xml. Show all posts

Sunday, March 11, 2012

Changing schemas already in an xml schema collection

One more question for the day.
I tried to alter the schema collection but only see the "ADD" keyword, which
then gives error about duplicate of same namespace. Seems like their needs
to be a "REPLACE" keyword instead of the ADD?
I also noticed that when you want to change a schema in the collection, you
have apparently have to do the following:
1) alter table/alter column to remove schema collection from column
2) drop schema collection
3) create schema collection
4) alter table/atler column to re-add schema collection
Any dangers in this procedure? Do you mess up the indexes or any
performance problems when you want to change a schema in a collection?
I guess it would be obvious that you could not remove an XML element if that
element is actually stored in the database, but i could see quite often
adding new elements to the schema.
Thanks in advance,
Neal Walters
http://Biztalk-Training.comHi Neal,
Yes, you're right. In SQL Server 2005 there is no replace or delete a schema
from a schema collection. It's popular as a "sought-after for next release"
feature. You may be able to add definitions to an existing schema in a
collection (using ALTER...ADD) as long as they don't redefine existing
types.
Your process seems right, and I'd drop/rebuild the indexes when I dropped
the schema collection from the column/and added the schema collection back
to the column. The documents in the XML column are re-validated when you
alter the table to add the schema collection back, this may take some time,
depending on how many rows you have.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Neal Walters" <NealWalters@.discussions.microsoft.com> wrote in message
news:ACA61EC4-D430-4E81-B4ED-021CD2A8ABB5@.microsoft.com...
> One more question for the day.
> I tried to alter the schema collection but only see the "ADD" keyword,
> which
> then gives error about duplicate of same namespace. Seems like their
> needs
> to be a "REPLACE" keyword instead of the ADD?
> I also noticed that when you want to change a schema in the collection,
> you
> have apparently have to do the following:
> 1) alter table/alter column to remove schema collection from column
> 2) drop schema collection
> 3) create schema collection
> 4) alter table/atler column to re-add schema collection
> Any dangers in this procedure? Do you mess up the indexes or any
> performance problems when you want to change a schema in a collection?
> I guess it would be obvious that you could not remove an XML element if
> that
> element is actually stored in the database, but i could see quite often
> adding new elements to the schema.
> Thanks in advance,
> Neal Walters
> http://Biztalk-Training.com
>|||Bob, Thanks for all your responses!
Neal

Changing schemas already in an xml schema collection

One more question for the day.
I tried to alter the schema collection but only see the "ADD" keyword, which
then gives error about duplicate of same namespace. Seems like their needs
to be a "REPLACE" keyword instead of the ADD?
I also noticed that when you want to change a schema in the collection, you
have apparently have to do the following:
1) alter table/alter column to remove schema collection from column
2) drop schema collection
3) create schema collection
4) alter table/atler column to re-add schema collection
Any dangers in this procedure? Do you mess up the indexes or any
performance problems when you want to change a schema in a collection?
I guess it would be obvious that you could not remove an XML element if that
element is actually stored in the database, but i could see quite often
adding new elements to the schema.
Thanks in advance,
Neal Walters
http://Biztalk-Training.com
Hi Neal,
Yes, you're right. In SQL Server 2005 there is no replace or delete a schema
from a schema collection. It's popular as a "sought-after for next release"
feature. You may be able to add definitions to an existing schema in a
collection (using ALTER...ADD) as long as they don't redefine existing
types.
Your process seems right, and I'd drop/rebuild the indexes when I dropped
the schema collection from the column/and added the schema collection back
to the column. The documents in the XML column are re-validated when you
alter the table to add the schema collection back, this may take some time,
depending on how many rows you have.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Neal Walters" <NealWalters@.discussions.microsoft.com> wrote in message
news:ACA61EC4-D430-4E81-B4ED-021CD2A8ABB5@.microsoft.com...
> One more question for the day.
> I tried to alter the schema collection but only see the "ADD" keyword,
> which
> then gives error about duplicate of same namespace. Seems like their
> needs
> to be a "REPLACE" keyword instead of the ADD?
> I also noticed that when you want to change a schema in the collection,
> you
> have apparently have to do the following:
> 1) alter table/alter column to remove schema collection from column
> 2) drop schema collection
> 3) create schema collection
> 4) alter table/atler column to re-add schema collection
> Any dangers in this procedure? Do you mess up the indexes or any
> performance problems when you want to change a schema in a collection?
> I guess it would be obvious that you could not remove an XML element if
> that
> element is actually stored in the database, but i could see quite often
> adding new elements to the schema.
> Thanks in advance,
> Neal Walters
> http://Biztalk-Training.com
>
|||Bob, Thanks for all your responses!
Neal

Wednesday, March 7, 2012

Changing output order for xml explicit

This one shouldn't be complicated or unusual but it's got me stumped!
Here's an example query.
select 1 as Tag,
0 as Parent,
us_id as [Person!1!Person!hide],
'' as [Address!2!Address1!element],
'' as [Address!2!Postcode!element],
us_telephone as [Person!1!Telephone!element]
from tblUser
union
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
us_telephone as [Person!1!Telephone!element]
from tblUser
order by [Person!1!Person!hide]
for xml explicit
This gives the output :-
<Person>
<Telephone>t1</Telephone>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
</Person>
The output I require is :-
<Person>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
<Telephone>t1</Telephone>
</Person>
i.e. put the telephone number after the address.
The application this is for is currently on SQLServer 2000, but can be
migrated to 2005 if that would help me get the data in the required format.
Thanks for any help.Hello maggi,
The order follows the order in your query have you tried the query below.
In addition you are populating the telephone number in the select for the
address element (Tag 2) even through it is populated on the main Person elem
ent
(Tag 1).
This is only required if you need to order by telephone number.
select 1 as Tag,
0 as Parent,
us_id as [Person!1!Person!hide],
us_telephone as [Person!1!Telephone!element]
'' as [Address!2!Address1!element],
'' as [Address!2!Postcode!element],
from tblUser
union
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
null
from tblUser
order by [Person!1!Person!hide]
for xml explicit
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> This one shouldn't be complicated or unusual but it's got me stumped!
> Here's an example query.
> select 1 as Tag,
> 0 as Parent,
> us_id as [Person!1!Person!hide],
> '' as [Address!2!Address1!element],
> '' as [Address!2!Postcode!element],
> us_telephone as [Person!1!Telephone!element]
> from tblUser
> union
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> us_telephone as [Person!1!Telephone!element]
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
> This gives the output :-
> <Person>
> <Telephone>t1</Telephone>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> </Person>
> The output I require is :-
> <Person>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> <Telephone>t1</Telephone>
> </Person>
> i.e. put the telephone number after the address.
> The application this is for is currently on SQLServer 2000, but can be
> migrated to 2005 if that would help me get the data in the required
> format.
> Thanks for any help.
>|||In 62959f1a501d68c92bc11929e988@.msnews.microsoft.com, Simon Sabin
<SimonSabin@.noemail.noemail> typed:

> The order follows the order in your query have you tried the query
> below. In addition you are populating the telephone number in the select
> for
> the address element (Tag 2) even through it is populated on the main
> Person element (Tag 1).
> This is only required if you need to order by telephone number.
> select 1 as Tag,
> 0 as Parent,
> us_id as [Person!1!Person!hide],
> us_telephone as [Person!1!Telephone!element]
> '' as [Address!2!Address1!element],
> '' as [Address!2!Postcode!element],
> from tblUser
> union
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> null
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
Cheers Simon, but that still wouldn't sort the telephone number AFTER the
address details to give me
<Person>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
<Telephone>t1</Telephone>
</Person>
I've given up on it in SQL Server 2000, and rewritten it using XML AUTO for
2005. I'd still be interested to know if anyone ever figures out a method
for doing it though.|||Try this...
select 1 as Tag,
NULL as Parent,
us_id as [Person!1!Person!hide],
NULL as [Address!2!Address1!element],
NULL as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
union all
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
union all
select 3 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
NULL as [Address!2!Address1!element],
NULL as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
order by [Person!1!Person!hide]
for xml explicit
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"maggi" <maggi_v@.gazeta.pl.invalid> wrote in message
news:eshutp$b8e$1@.inews.gazeta.pl...
> In 62959f1a501d68c92bc11929e988@.msnews.microsoft.com, Simon Sabin
> <SimonSabin@.noemail.noemail> typed:
>
> Cheers Simon, but that still wouldn't sort the telephone number AFTER the
> address details to give me
> <Person>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> <Telephone>t1</Telephone>
> </Person>
> I've given up on it in SQL Server 2000, and rewritten it using XML AUTO
> for 2005. I'd still be interested to know if anyone ever figures out a
> method for doing it though.
>|||In uWrpmZEYHHA.992@.TK2MSFTNGP02.phx.gbl, Peter W. DeBetta
<debettap@.hotmail.com> typed:

> Try this...
> select 1 as Tag,
> NULL as Parent,
> us_id as [Person!1!Person!hide],
> NULL as [Address!2!Address1!element],
> NULL as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> union all
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> union all
> select 3 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> NULL as [Address!2!Address1!element],
> NULL as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
That's the one!
Thank you very much, Peter.

Tuesday, February 14, 2012

Changing document type definition when using FOR XML AUTO

Hi
When using FOR XML AUTO in sql server is there a way to change the
document typ definition (<?xml version="1.0"?> ). What I wanna do is
add the encoding property so it instead of the above reads <?xml
version="1.0" encoding="ISO-8859-1"?>.
The reason I wanna do this is because I have characters like "" and
"".
Or is this something I can handle in my .asp-page?
Thankfull for all suggestions!
/Christer
ASP code:
<%
Response.Buffer = true
'Response.ContentType = "text/xml"
Dim cn, rs,sQuery, cmd, xmldoc
Const adExecuteStream = &H00000400
Const adCmdText = &H0001
Set cn = Server.CreateObject("ADODB.Connection")
cn.open CnString
Set xmldoc = Server.CreateObject("MSXML2.DomDocument")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
sQuery = "getDiaryXML"
cmd.CommandText = sQuery
cmd.Properties("xml root").Value = "root"
cmd.Properties("Output Stream") = xmldoc
cmd.Execute , , adExecuteStream + adCmdText
Set cmd = Nothing
cn.Close
Set cn = Nothing
%>
<%=xmldoc.xml%>
***********************
SQL:
SELECT
YEAR(dtDate) AS strYear,
DATENAME(MONTH,dtDate) AS strMonth,
DAY(dtDate) AS strDay,
diaryID,
txtDiaryHeader,
txtDiaryIngress,
txtDiaryText,
id as photoID
FROM diary ORDER BY dtDate
FOR XML AUTOHi
I think the problem is that the encoding should be UTF-16 this may help
http://groups-beta.google.com/group...c?
hl=en
John
"Christer" wrote:

> Hi
> When using FOR XML AUTO in sql server is there a way to change the
> document typ definition (<?xml version="1.0"?> ). What I wanna do is
> add the encoding property so it instead of the above reads <?xml
> version="1.0" encoding="ISO-8859-1"?>.
> The reason I wanna do this is because I have characters like "?" and
> "?".
> Or is this something I can handle in my .asp-page?
> Thankfull for all suggestions!
> /Christer
> ASP code:
> <%
> Response.Buffer = true
> 'Response.ContentType = "text/xml"
> Dim cn, rs,sQuery, cmd, xmldoc
> Const adExecuteStream = &H00000400
> Const adCmdText = &H0001
> Set cn = Server.CreateObject("ADODB.Connection")
> cn.open CnString
> Set xmldoc = Server.CreateObject("MSXML2.DomDocument")
> Set cmd = Server.CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = cn
> sQuery = "getDiaryXML"
> cmd.CommandText = sQuery
> cmd.Properties("xml root").Value = "root"
> cmd.Properties("Output Stream") = xmldoc
> cmd.Execute , , adExecuteStream + adCmdText
> Set cmd = Nothing
> cn.Close
> Set cn = Nothing
> %>
> <%=xmldoc.xml%>
> ***********************
> SQL:
> SELECT
> YEAR(dtDate) AS strYear,
> DATENAME(MONTH,dtDate) AS strMonth,
> DAY(dtDate) AS strDay,
> diaryID,
> txtDiaryHeader,
> txtDiaryIngress,
> txtDiaryText,
> id as photoID
> FROM diary ORDER BY dtDate
> FOR XML AUTO
>