cdata all columns automatically

I have an application that is extremely dynamic.
Users can type in their own sql statements and run queries however they
want.
Is there a way using SQL to automaticall wrap all the columns with a
CDATA notation?
EX:
Query
SELECT field1, field2, field3 FROM myTable FOR XML ...
Desired result
<row>
<field1><![CDATA[field1 data]]></field1>
<field2><![CDATA[field2 data]]></field2>
<field3><![CDATA[field3 data]]></field3>
</row>
**PLEASE NOTE** I have no control of the SELECT statement, the users
can select whatever they want. So i cannot use the regular EXPLICIT
col!cdata notation :(And why would you want to wrap the data into a CDATA section? CDATA sections
are there for lazy hand authors of XML documents that do not want to write
< or & everytime they need to write < or &. On the output-side CDATA
should not make any difference whatsoever since FOR XML automatically does
the needed entitization.
Best regards
Michael
"jamesd" <jamesd@.ring4freedom.com> wrote in message
news:1164860203.165600.219350@.j44g2000cwa.googlegroups.com...
>I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation :(
>

cdata all columns automatically

> I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation :(
Did anyone have a solution for this? I need the cdata tags for doublebyte v
alues.
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilitiesFOR XML EXPLICIT without assigning it to an XML datatype can provide you
with CDATA sections. Note however, that there is really no reason to
generate CDATA sections since FOR XML is entitizing the characters for you
that you would otherwise have to wrap into a CDATA section.
There is no semantic difference between <a><![CDATA[a]]></a> and <a>a</a>.
Best regards
Michael
"kira" <HappyGirl101@.gmail.com> wrote in message
news:31cd9f77-c21c-439f-b6f2-169e2776756d@.text.giganews.com...
>
> Did anyone have a solution for this? I need the cdata tags for doublebyte
> values.
> BizTalk Utilities - Frustration free BizTalk Adapters
> http://www.topxml.com/biztalkutilities

cdata all columns automatically

I have an application that is extremely dynamic.
Users can type in their own sql statements and run queries however they
want.
Is there a way using SQL to automaticall wrap all the columns with a
CDATA notation?
EX:
Query
SELECT field1, field2, field3 FROM myTable FOR XML ...
Desired result
<row>
<field1><![CDATA[field1 data]]></field1>
<field2><![CDATA[field2 data]]></field2>
<field3><![CDATA[field3 data]]></field3>
</row>
**PLEASE NOTE** I have no control of the SELECT statement, the users
can select whatever they want. So i cannot use the regular EXPLICIT
col!cdata notation
And why would you want to wrap the data into a CDATA section? CDATA sections
are there for lazy hand authors of XML documents that do not want to write
< or & everytime they need to write < or &. On the output-side CDATA
should not make any difference whatsoever since FOR XML automatically does
the needed entitization.
Best regards
Michael
"jamesd" <jamesd@.ring4freedom.com> wrote in message
news:1164860203.165600.219350@.j44g2000cwa.googlegr oups.com...
>I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation
>
|||
> I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation
Did anyone have a solution for this? I need the cdata tags for doublebyte values.
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities
|||FOR XML EXPLICIT without assigning it to an XML datatype can provide you
with CDATA sections. Note however, that there is really no reason to
generate CDATA sections since FOR XML is entitizing the characters for you
that you would otherwise have to wrap into a CDATA section.
There is no semantic difference between <a><![CDATA[a]]></a> and <a>a</a>.
Best regards
Michael
"kira" <HappyGirl101@.gmail.com> wrote in message
news:31cd9f77-c21c-439f-b6f2-169e2776756d@.text.giganews.com...
>
> Did anyone have a solution for this? I need the cdata tags for doublebyte
> values.
> BizTalk Utilities - Frustration free BizTalk Adapters
> http://www.topxml.com/biztalkutilities

Cause INSERT statements without column lists to fail

Hi Hilary, Paul....
I have another real problem I hope you can help with.
Setup replication now with no problems but one!
The database tables fail from the application thats using them because of
the ROWQUID column.
I noticed that JUST before the articles are created it shows this;
SQL Server requires that all merge articles contain a uniqueidentifier
column with a unique index and the ROWGUIDCOL property. SQL Server will add a
uniqueidentifier column to published tables that do not have one when the
first snapshot is generated.
Adding a new column will:
? Cause INSERT statements without column lists to fail
? Increase the size of the table
? Increase the time required to generate the first snapshot
So is there ANY way of getting round this bug because my developer said that
they will not alter their software and the way the tables are accessed.
So, any ideas.
First you have to find out if the code does do something like this
Insert into tablename
select * from anothertablename
This is what will break with merge replication. Ask him specifically about
this. Personally good developers would never do anything like this. They
would also access the database through stored procedures which would allow
you, the dba, to modify something like this.
Another method is to have the application talk to a view which only contains
the non rowguid column. This isn't very scalable.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
""confused"" <confused@.discussions.microsoft.com> wrote in message
news:53394C5B-05FA-418C-99CA-FB8A8FF5769A@.microsoft.com...
> Hi Hilary, Paul....
> I have another real problem I hope you can help with.
> Setup replication now with no problems but one!
> The database tables fail from the application thats using them because of
> the ROWQUID column.
> I noticed that JUST before the articles are created it shows this;
> SQL Server requires that all merge articles contain a uniqueidentifier
> column with a unique index and the ROWGUIDCOL property. SQL Server will
> add a
> uniqueidentifier column to published tables that do not have one when the
> first snapshot is generated.
> Adding a new column will:
> Cause INSERT statements without column lists to fail
> Increase the size of the table
> Increase the time required to generate the first snapshot
> So is there ANY way of getting round this bug because my developer said
> that
> they will not alter their software and the way the tables are accessed.
> So, any ideas.
|||Hi,
No it looks like they have hard coded this one particluar SQL statement.
So what acutally changes in the database that affects the table INSERT
commands?.
Is their a way round this. If there isnt then its back to square one for
me...
Thanks
"Hilary Cotter" wrote:

> First you have to find out if the code does do something like this
> Insert into tablename
> select * from anothertablename
> This is what will break with merge replication. Ask him specifically about
> this. Personally good developers would never do anything like this. They
> would also access the database through stored procedures which would allow
> you, the dba, to modify something like this.
> Another method is to have the application talk to a view which only contains
> the non rowguid column. This isn't very scalable.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> ""confused"" <confused@.discussions.microsoft.com> wrote in message
> news:53394C5B-05FA-418C-99CA-FB8A8FF5769A@.microsoft.com...
>
>
|||You say one particular SQL Statement - is it in a stored procedure? If so,
just modify the stored procedure so it contains all the column name except
the rowguid column.
ie before
insert into mytable
select * from myothertable
after
insert into mytable
select col1,col2, col3, col4,... from myothertable.
If this can't be done change the table the app writes to so it is now a
view. So if the app wrote to Mytable rename mytable as mytable and create a
view called mytable. This view will look like this create view mytable as
select col1, col2, col3, col4,... from mytable1
This doesn't scale well. How many nodes in your system? You might want to
look at bi-directional transactional replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
""confused"" <confused@.discussions.microsoft.com> wrote in message
news:8DEDF1FC-8926-4072-9D22-91715B8FEE3D@.microsoft.com...[vbcol=seagreen]
> Hi,
> No it looks like they have hard coded this one particluar SQL statement.
> So what acutally changes in the database that affects the table INSERT
> commands?.
> Is their a way round this. If there isnt then its back to square one for
> me...
> Thanks
> "Hilary Cotter" wrote:
|||Hi Hilary,
I think I found the problem but I dont know how to get to it.
The dependencies show 3 objects that have a green plus sign next to them.
These objects are PHONE_ADD, PHONE_DELETE, PHONE_AMEND but I cant see these
anywhere in the database?.
Any ideas?
TIM
"Hilary Cotter" wrote:

> You say one particular SQL Statement - is it in a stored procedure? If so,
> just modify the stored procedure so it contains all the column name except
> the rowguid column.
> ie before
> insert into mytable
> select * from myothertable
> after
> insert into mytable
> select col1,col2, col3, col4,... from myothertable.
> If this can't be done change the table the app writes to so it is now a
> view. So if the app wrote to Mytable rename mytable as mytable and create a
> view called mytable. This view will look like this create view mytable as
> select col1, col2, col3, col4,... from mytable1
> This doesn't scale well. How many nodes in your system? You might want to
> look at bi-directional transactional replication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> ""confused"" <confused@.discussions.microsoft.com> wrote in message
> news:8DEDF1FC-8926-4072-9D22-91715B8FEE3D@.microsoft.com...
>
>
|||Where are you seeing these green arrows?
Can you do the following for me?
select name from sysobjects where name in (PHONE_ADD, PHONE_DELETE,
PHONE_AMEND) and type='u'
GO
select object_name(id),name from syscolumns where name in (PHONE_ADD,
PHONE_DELETE, PHONE_AMEND)
GO
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
""confused"" <confused@.discussions.microsoft.com> wrote in message
news:ACD9DC12-A2C2-4E65-83C8-3D4E39EE8109@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> I think I found the problem but I dont know how to get to it.
> The dependencies show 3 objects that have a green plus sign next to them.
> These objects are PHONE_ADD, PHONE_DELETE, PHONE_AMEND but I cant see
> these
> anywhere in the database?.
> Any ideas?
> TIM
> "Hilary Cotter" wrote:

CASTING statement for a date

I am selecting older legacy data from an AS400 mainframe that we still use. I am fairly new to constructing T-SQL statements so I hope I am doing this correctly. There is a table from the AS400 that has been setup with a field for TMONTH, TDAY, and TYEAR. Instead of having one field for a date, for some reason years ago this was set up this way.

I now have this statement in my SELECT statement and it is not working:

WHERE (CAST(OWNR.TMONTH + '/' + OWNR.TDAY + '/' + OWNR.TYEAR AS DATETIME) >= @.startdate)

I am not getting a syntax error, however I am getting "Error Converting data type varchar to numeric. These fields on the AS400 are set up as numeric fields.

What do I need to do differently? Should I use a CONVERT instead and if so, how would I structure that statement.

Thanks for the help

SQL Server will assume if one value is numeric that there has to be done an addition rather than a concatenation. So you will have to CAST all the numerics to chars like that:

WHERE (CAST(CAST(OWNR.TMONTH AS VARCHAR(2))+ '/' + CAST(OWNR.TDAY AS VARCHAR(2)) + '/' + CAST(OWNR.TYEAR AS VARCHAR(2)) AS DATETIME) >= @.startdate)

Or something like that:

WHERE CAST((100*OWNR.TMONTH + OWNR.TDAY + OWNR.TYEAR*10000) AS DATETIME) >= @.startdate)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

You may want to have a look at the answer to question 2 that I received in my post for Questions on dates. Kenneth has provided a means for using an international date format that is simple.

Code is:

cast(@.year as char(4)) + right('0' + cast(@.month as varchar(2)),2) + right('0' + cast(@.day as varchar(2)),2)

which may be assigned to a date variable. You will need to ensure that you have error checking to ensure that the date is valid.

Regards,

Flavelle