Question regarding Sql server 2000 column names and aliases

Question

Post reply

Question regarding Sql server 2000 column names and aliases

salio

Newbie

Points: 5
More actions
December 6, 2004 at 5:42 pm

#162855

Hello,
I have a question regarding Sql server 2000 column names and aliases. I have a table with two columns
Column1 name is MedicationID
Column2 name is MedicationName
I use for xml to retreive data from this table but the XML is huge because for every tag there is <Medication MedicationID = "..." MedicationName = "..."/>.
On the client-side I am using the .NET framework 2003. The application I am working on has a strongly typed dataset so the column names on the dataSet are similar to the ones in the table in SQL server. Is it possible to have aliases sent in the XML that would map themselves to the full column names? Like for example I would like to have <Med MID = "..." MName = "..."/> sent across without changing the full column names on the server or client side.
Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6134 More actions · Answer 1

It's not clear what you're really asking but perhaps what you're looking for is (are) views.

You can create a view which has all the columns in the base table but renames them.

salio Newbie Points: 5 More actions · Answer 2

Below is part of the stored procedure that returns the xml:

select '<MDS>'

select 1 as TAG,

0 as parent,

Q1.MedicationID as [Medication!1!MedicationID],

Q1.PatientID as [Medication!1!MedicationName]

from Medication Q1

WHERE Q1.MedicationID = @MedicationID

FOR XML EXPLICIT

select '</MDS>'

This sp returns xml of format

I Know how to acheive the following xml already

which is by changing the tag [Medication!1!MedicationID] to [M!1!MID]

The xml is read into a string which is then into a dataset on the clientside. The dataset structure on the clientside is the same as the one on the SQL server. Which means that the Medication table is setup as Medication table and the MedicationID column is setup as MedicationID column.

My question is when I change the bigger tag to the smaller tag what do I need to do to make the

Database and the Dataset understand that the M actually means Medication and MID actually means MedicationID and so on?

Thanks in advance.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

What your talking about is how to control the effect at the client. That all depends on how you implemented there. I haven't had a chance to do much with XML except very simply thing myself, but if you can provide the relatvent code to retrieving the data and storing I can figure out unless someone else jumps in with the solution. However I will not be able to do much until the weekend.

salio Newbie Points: 5 More actions · Answer 4

Here is the code from the client side application written in C# .NET

private void LoadDataSet(string returnxml)

{

System.IO.StringReader xmlSR = new System.IO.StringReader(returnXml);

dsMedication.ReadXml(xmlSR,XmlReadMode.Auto);

//Other functionality here

}

As you can see the above code reads in the xml string directly into the dataset. If there are too many medications then the Xml is HUGE because of Medication spelled out for the ID and the Name. If there was a way to shorten it for the data transfer then when it is read in it would know that the M stands for Medication. I am hoping for a solution without manipulating the actual xml string and replacing the M with the Medication because that would effect the performance of the application.

Thanks in advance.

Calvin Lawson SSChampion Points: 11030 More actions · Answer 5

I don't understand, why don't you just use the following?

select '<MDS>'

select 1 as TAG,

0 as parent,

Q1.MedicationID as [M!1!MID],

Q1.PatientID as [M!1!MName]

from Medication Q1

WHERE Q1.MedicationID = @MedicationID

FOR XML EXPLICIT

select '</MDS>'

Signature is NULL

salio Newbie Points: 5 More actions · Answer 6

I think I haven't explained the whole picture very clearly. Let me start from the begining. I am using C# .NET V1.1 for an application that will retreive data from a SQL 2000 database server.

Below is part of the stored procedure that returns the xml:

select '<MDS>'

select 1 as TAG,

0 as parent,

Q1.MedicationID as [Medication!1!MedicationID],

Q1.PatientID as [Medication!1!MedicationName]

from Medication Q1

WHERE Q1.MedicationID = @MedicationID

FOR XML EXPLICIT

select '</MDS>'

This sp returns xml of format

The xml is read into a string which is then into a dataset on the clientside. The dataset structure on the clientside is the same as the one on the SQL server, i.e: the table name on SQL server is Medication and the one on the strongly typed dataset is also Medication and the column names are MedicationID and MedicationName respectively. Full names are needed for the client application.

Here is the code from the client side application written in C# .NET that accesses the Xml string

private void LoadDataSet(string returnxml)

{

System.IO.StringReader xmlSR = new System.IO.StringReader(returnXml);

dsMedication.ReadXml(xmlSR,XmlReadMode.Auto);

//Other functionality here

}

As you can see the above code reads in the xml string directly into the dataset. If there are too many medications then the Xml is HUGE because of the word 'Medication' spelled out in full.

I Know that there is a way to shorten the length of the xml for the data transfer by doing the following:

which is by changing the tag [Medication!1!MedicationID] to [M!1!MID] and [Medication!1!MedicationName] to [M!1!MName]

My question is how would the dataset or the SQL server know that the M stands for Medication table and the MID stands for the MedicationID column and the MName stands for the MedicationName column? I am hoping for a solution without manipulating the actual xml string and replacing the M with the Medication because that would have an effect on the performance. Is there a way to configure an alias for the database server and the dataset table columns so that the M automatically maps to the full name?

Thanks in advance.

Calvin Lawson SSChampion Points: 11030 More actions · Answer 7

Ah! No...sorry, not that I'm aware of...not automatically.

Basically, you're requiring two things, the client app needs full names, and you want a small xml file. The problem is these requirements are at odds with each other, as the tags define which column each value is associated with. So you either need to change your requirement for full names, or change your requirement for a small xml file. This is oversimplified, but essentially true.

Why does the client app need full names? It seems this is only important in the presentation layer.

cl

Signature is NULL

salio Newbie Points: 5 More actions · Answer 8

Hi Old Hand,

You are absolutely correct that it is only important in the presentation layer but the application is already in use with the long names. To go back and change the code would require a lot of work. I was hoping for a shorter solution for such a scenario.

Thanks.

Calvin Lawson SSChampion Points: 11030 More actions · Answer 9

Yeah, that's a dilly of a pickle. The only thing I could suggest is a data transformation between the call to the database and sending the data to the presentation layer. Some sort of middle layer recordset to xml conversion, probably stupidly simple.

Of course, the most obvious answer would be to change the presentation layer, as it is causing a performance problem with it's requirements.

Good luck!

cl

Signature is NULL