Marking copied records

I have a big problem now. I need to write a SQL statement to copy some
records from a table to another table. At the process of copying, I want to
update a field in both table. The field is to identify whether the record is
the 1st, 2nd, 3rd, 4th, ... record that I've copied, which will be in runnin
g
sequence. There should be repeating numbers. The reason for doing this is if
a user modifies a record in the new table in the future, I will still know
how it originally was by referring back by that number.
Do you get what I mean? I have no idea whether SQL can do that. And whether
it can be settled in a statement. Can someone help me? Give me some guide?
Thank you.You could do this by adding a DateCopied (datetime -default getdate() )
column to the tables -perhaps even adding a WhoChanged (varchar(50) -default
system_user) column.
Other options include a Sequence (timeStamp datatype) Column.
Either of these choices would allow you to always restructure the sequence
of data changes.
Then you just add a TRIGGER to the primary table to copy the old version to
the archive table whenever there is a data change.
You might google "SQL Server" and "Audit Trail". Here's an article to get
you started:
http://expertanswercenter.techtarge...i980058,00.html
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:8D4B4729-8170-436E-BE05-D87933758E59@.microsoft.com...
>I have a big problem now. I need to write a SQL statement to copy some
> records from a table to another table. At the process of copying, I want
> to
> update a field in both table. The field is to identify whether the record
> is
> the 1st, 2nd, 3rd, 4th, ... record that I've copied, which will be in
> running
> sequence. There should be repeating numbers. The reason for doing this is
> if
> a user modifies a record in the new table in the future, I will still know
> how it originally was by referring back by that number.
> Do you get what I mean? I have no idea whether SQL can do that. And
> whether
> it can be settled in a statement. Can someone help me? Give me some guide?
> Thank you.

Marking a table as a System Table

Okay, most peoples answer to this may be "Gaaah. Why would you do
this?", or the like, but here's the question anyway:

Are there any stability issues if I mark one of my user tables as a
system table (by switching xtype in sysobjects from 'S' to 'U')?

I'm not doing this as "a cleved bit of security" or some such - my
actual reason for doing this is so that some of my automatic generation
tools do not process this particular table, and I want a method that
will not mean updating each of the tools if I ever add another table
like this.

It APPEARS to work, based on a quick trial, but has anybody got any
direct experience of this? Any horror stories like "Well, it worked
fine for two weeks, then it shot my co-workers, set fire to the company
accounts, and urinated in a corner. Then things got worse"

Also, yes, yes, yes, I do not necessarily expect this to work in future
releases of SQL Server (currently on 2000), but I should avoid naming
conflicts by the fact that the owner isn't dbo.
Thanks in advance for any insights."Damien" wrote:

> Okay, most peoples answer to this may be "Gaaah. Why would you do
> this?", or the like, but here's the question anyway:
> Are there any stability issues if I mark one of my user tables as a
> system table (by switching xtype in sysobjects from 'S' to 'U')?
> I'm not doing this as "a cleved bit of security" or some such - my
> actual reason for doing this is so that some of my automatic generation
> tools do not process this particular table, and I want a method that
> will not mean updating each of the tools if I ever add another table
> like this.
> It APPEARS to work, based on a quick trial, but has anybody got any
> direct experience of this? Any horror stories like "Well, it worked
> fine for two weeks, then it shot my co-workers, set fire to the company
> accounts, and urinated in a corner. Then things got worse"
> Also, yes, yes, yes, I do not necessarily expect this to work in future
> releases of SQL Server (currently on 2000), but I should avoid naming
> conflicts by the fact that the owner isn't dbo.
> Thanks in advance for any insights.

Well I changed a system table once and all the workers recovered, the
building only smoldered, no one noticed the puddle, and then things seemed
to clear up on their own :)

Seriously, though, why not use extended properties for this purpose? For
your table that you don't want to process, use...

exec sp_addextendedproperty
'DoNotProcess',
'1',
'USER',
'dbo',
'TABLE',
'TableThatIsntProcessed',
NULL,
NULL

...Then before you process any table you can...

if not exists (
select null
from ::fn_listextendedproperty(
'DoNotProcess',
'USER', 'dbo',
'TABLE', 'TableThatIsntProcessed',
NULL, NULL
)
where value = '1'
)
begin
print 'process table'
end

This way any table can be selectively set up for processing or not without
touching the system tables and you have a better chance of not getting
broken with succesive releases of SQL Server.

Craig|||Thanks, I'll look into that. I've not used extended properties before
(then again, we've only just upgraded from 7 (as in, last weekend), was
it available in 7?)|||"Damien" wrote:

<snip>
> was it available in 7?

<snip
http://www.microsoft.com/sql/techin...dproperties.asp

According to this link, no (the page above also contains links to articles
on using extended properties, although I didn't follow them to see if they
were still good). It's been a while since we upgraded from 7 to 2K, but
that also seems sync with my memory.

Craigsql

Mappings question in OLE DB Destination

Hi,

I have a situation where I want to map a column from a flat file to TWO columns in a table.

However, in the mappings tab, you can only select the "Input Column" once. Once a column has been used, it no longer appears in the drop down list.

I am wondering if there's a way to override this behavior, and if not, what is the best way to handle this type of situation?

I have added an EXECUTE SQL task to update the second column with the inserted column values, but I would like to know if the default mapping behavior can be changed, as it seems so limited.

Thanks

Add a derived column right before the destination and select the column that you want to use more than once and drag it to the expression box. Adjust the name of the new column accordingly.

Then in the OLE DB Destination you can select the column you just added.

Feel free to suggest new features over at http://connect.microsoft.com/sqlserver/feedback|||

Great, thanks

sql

mapping XML data to variable

I can’t figure out how to map xml data stored in a table to a variable in integration service.

For example:
I would like to use a “for each loop container” to iterate through a row set selected from database. Each row has three columns, an integer, a string and an xml data. In the variable mappings, I can map the integer column and the string column to a variable with type of int and a variable with type of string. But I am having trouble to map the xml data column to any variable. I tried using either a string variable or object. It always reports error like “variable mapping number X to variable XXX can’t apply”.

Any help?

This is a supported scenario. Ensure that:

The column is actually being loaded into the record set: Check the column mappings in the recordset dest The value being mapped to the variable is less than 4000 characters long: select max(datalength(xmlCol)) from XmlTable

Mapping UDF Parameters to Variables

As mentioned in a previous posting, I have an in-line table valued UDF with three input parameters. I can set this up as an OLEDB Datasource SQL Command Text with parameter markers (i.e. "?") and test it successfully in the Generic Query Builder. The parameter markers are correctly associated with the input parameters of the UDF and the parameters can be entered at execution time into a parameters table.

So near and yet so far. When I attempt to map the parameter markers with Package Variables there is an error message saying the the parameter details cannot be retrieved from the function. If the function was in a foriegn (e.g. Oracle) database I might accept this as just one of those things but this is a SQL 2005 database and compatability should be complete. Add to this that the Generic Query Builder has no problem with the same UDF and nor does Reporting Services and I have to assume that this is a bug plain and simple.

The only solution that I have seen suggested is to embed the SQL Command text in a Package Variable and change it at execution time but I regard this as a second rate solution.

Dick,

This doesn't help you, but I think there IS a bug with the OLEDB data source and passing variables. I have exactly the same issue with a simple query to a Microsoft FoxPro data source (which I've brought to this forum before and got no solution).

I went with the package variable solution - it's not as elegant but it works

Do you know where we should post bug reports for SQL 2005?

Rich

|||

Thanks Rich,

It's some consolation the hear from someone else about the problem. Foxpro is a "foriegn" application (albiet a Microsoft one). It's the fact that the problem occurs within SQL Server 2005 that is a bit of a surprise. Add to this the fact that Reporting Services seems to handle UDF parameters correctly (indicating that there is no reason why it shouldn't work) and it is a bit frustrating.

As you say contructing the entire SQL Command Text as a Package Variable is a work around but this is not really what was intended, Mapping Package Variables to parameter markers is more within the spirit of SSIS and much nicer. It seems to work with Stored Procedures for example.

My company has links with MS so I will try to find out on Monday how to officially report this.

Best regards,

Dick Campbell

mapping two references from one table ?

Hi, I'm new to SQL programming and SQL 2000 server.
My problem is that I have a table that contains antenna information.
(Their can be different types of antenna's)
I have another table that points to the antenna table twice, because it
needs two different types of antennas. IE: GPS and radio
When I try to map the results, I don't know how to specify which one I
want to reference.
For example: This is what the query builder builds for me
SELECT DrtType.TxAntennaId, DrtType.GpsAntennaId, Antenna.AntennaId,
Antenna.AntennaName FROM DrtType INNER JOIN Antenna ON
DrtType.TxAntennaId = Antenna.AntennaId AND DrtType.GpsAntennaId =
Antenna.AntennaId
When I try to format the datagrid. I need to map each value that I use
individually
//// Add GPS Antenna
DataGridColumnStyle TextCol_3 = new DataGridTextBoxColumn();
TextCol_3.MappingName = "AntennaName";
TextCol_3.HeaderText = "Gps Antenna";
TextCol_3.Width = defaultWidth;
dgStyle.GridColumnStyles.Add(TextCol_3);
But I only have one AntennaName to map.
Can I do something like:
DrtType.TxAntennaId.AntennaName
DrtType.GpsAntennaId.AntennaName
Thank you for you helpOn 29 Nov 2005 18:11:07 -0800, oracle wrote:

>Hi, I'm new to SQL programming and SQL 2000 server.
>My problem is that I have a table that contains antenna information.
>(Their can be different types of antenna's)
>I have another table that points to the antenna table twice, because it
>needs two different types of antennas. IE: GPS and radio
>When I try to map the results, I don't know how to specify which one I
>want to reference.
>For example: This is what the query builder builds for me
>SELECT DrtType.TxAntennaId, DrtType.GpsAntennaId, Antenna.AntennaId,
>Antenna.AntennaName FROM DrtType INNER JOIN Antenna ON
>DrtType.TxAntennaId = Antenna.AntennaId AND DrtType.GpsAntennaId =
>Antenna.AntennaId
Hi oracle,
You have to join the Antenna table twice, and use aliases to
distinguishe between the two:
SELECT D.TxAntennaId, D.GpsAntennaId,
TX.AntennaName AS TX_Antenna,
GPS.AntennaName AS GPS_Antenna
FROM DrtType AS D
INNER JOIN Antenna AS TX
ON TX.AntennaId = D.TxAntennaId
INNER JOIN Antenna AS GPS
ON GPS = D.GpsAntennaId
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Mapping table problem

HI guys
I need to put multiple coniditions on my table based on some combinations.

Here is my main table-

Practitioner

DHBName

PHOName

Practicename

PractitionerName

Locum

YCount

NCount

4

Canterbury DHB

Partnership Health (Canterbury)-596721

Linwood Avenue Medical Centre

Christine Abbasi

No

0

1

32

Canterbury DHB

Partnership Health (Canterbury)-596721

Felicia House Clinic (Pearson)

Nicci Pavey

No

0

1

197

Canterbury DHB

Partnership Health (Canterbury)-596721

Woodham Road Health Care

Peggy Shelton-Agar

No

0

1

394

Hawkes Bay DHB

Hawkes Bay PHO Limited-587862

The Doctors Napier

John Laird

YES

1

0

549

Canterbury DHB

Partnership Health (Canterbury)-596721

Linwood Avenue Medical Centre

Denise Armstrong

No

0

1

1286

Canterbury DHB

Partnership Health (Canterbury)-596721

Darfield Medical Centre Limited

Katherine Muscroft-Taylor

No

0

1

1501

Counties-Manukau DHB

Procare Network Manukau Limited-573183

Manukau City Health Centre

Caroline Shephard

YES

1

0

1932

Hawkes Bay DHB

Hawkes Bay PHO Limited-587862

Clive Medical Centre Ltd

Karen Irwin

YES

1

0

2226

Canterbury DHB

Partnership Health (Canterbury)-596721

Sumner Health Centre

Anne Scott

No

0

1

3243

Waitemata DHB

Harbour PHO Ltd-610733

Byron Medical

Rex Livingstone Sinclair

No

0

1

3263

Canterbury DHB

Partnership Health (Canterbury)-596721

Sumner Health Centre

Peter Wynn Nicholson

No

0

1

3381

Otago DHB

Otago Southern Region Primary Health Organisation-597004

Dr Visagie's Practice

Fay Young

NO

0

1

Here is my mapping table-

Locum Flag

No count

Yes count

Percentage

No

1

0

1

No

1

1

0.75

Yes

1

1

0.25

No

2

0

0.5

No

3

0

0.3333

No

4

0

0.25

Yes

2

1

0.125

No

2

1

0.75

Yes

0

1

0.75

Yes

0

2

0.375

Yes

0

3

0.25

Yes

0

4

0.1875

I have to add an additional column called 'percentage' in my main table by refering the mapping table.

For ex for a particular practitioner, Locum flag is 'Yes', nocount is 1 and yes count is 1, then percentage should be '0.25'

How should I do this?

So, when you are adding data to your main table, why not just add a chunk of code that would calculate what the percentage would be and keep the mapping table 'on paper' ? (And then store the result)|||

hi Tas_CRO

How do I add that chunk of code to my main table?

That is probably I need to know

Thanks

Mapping table problem

HI guys
I need to put multiple coniditions on my table based on some combinations.

Here is my main table-

PractitionerDHBNamePHONamePracticenamePractitionerNameLocumYCountNCount4Canterbury DHBPartnership Health (Canterbury)-596721Linwood Avenue Medical CentreChristine AbbasiNo0132Canterbury DHBPartnership Health (Canterbury)-596721Felicia House Clinic (Pearson)Nicci PaveyNo01197Canterbury DHBPartnership Health (Canterbury)-596721Woodham Road Health CarePeggy Shelton-AgarNo01394Hawkes Bay DHBHawkes Bay PHO Limited-587862The Doctors NapierJohn LairdYES10549Canterbury DHBPartnership Health (Canterbury)-596721Linwood Avenue Medical CentreDenise ArmstrongNo011286Canterbury DHBPartnership Health (Canterbury)-596721Darfield Medical Centre LimitedKatherine Muscroft-TaylorNo011501Counties-Manukau DHBProcare Network Manukau Limited-573183Manukau City Health CentreCaroline ShephardYES101932Hawkes Bay DHBHawkes Bay PHO Limited-587862Clive Medical Centre LtdKaren IrwinYES102226Canterbury DHBPartnership Health (Canterbury)-596721Sumner Health CentreAnne ScottNo013243Waitemata DHBHarbour PHO Ltd-610733Byron MedicalRex Livingstone SinclairNo013263Canterbury DHBPartnership Health (Canterbury)-596721Sumner Health CentrePeter Wynn NicholsonNo013381Otago DHBOtago Southern Region Primary Health Organisation-597004Dr Visagie's PracticeFay YoungNO01

Here is my mapping table-

Locum FlagNo countYes countPercentageNo101No110.75Yes110.25No200.5No300.3333No400.25Yes210.125No210.75Yes010.75Yes020.375Yes030.25Yes040.1875

I have to add an additional column called 'percentage' in my main table by refering the mapping table.

For ex for a particular practitioner, Locum flag is 'Yes', nocount is 1 and yes count is 1, then percentage should be '0.25'

How should I do this?

So, when you are adding data to your main table, why not just add a chunk of code that would calculate what the percentage would be and keep the mapping table 'on paper' ? (And then store the result)
|||

hi Tas_CRO

How do I add that chunk of code to my main table?

That is probably I need to know

Thanks

Mapping schema for GoogleBase XSD

I want to load into a SQL Server table with SQLXMLBulkload an xml file that
is formatted with google base xsd (http://base.google.com/base/base.xsd). I
need a mapping schema for that, correct? To map the elements of the xml to
my table. Does anyone have a mapping schema for this?
Steve
Hi Steve
The mapping depends on the database tables to which you want to map it to.
Best regards
Michael
"Steve Mc" <stevemc@.zillow.com> wrote in message
news:OGEN6NOiHHA.5052@.TK2MSFTNGP05.phx.gbl...
>I want to load into a SQL Server table with SQLXMLBulkload an xml file that
>is formatted with google base xsd (http://base.google.com/base/base.xsd).
>I need a mapping schema for that, correct? To map the elements of the xml
>to my table. Does anyone have a mapping schema for this?
> Steve
>
|||Hello,
Please take a look at http://msdn2.microsoft.com/en-us/library/ms172649.aspx
to understand how annotations work.
Let me know if you need further assistance.
Regards,
Monica Frintu
"Steve Mc" wrote:

> I want to load into a SQL Server table with SQLXMLBulkload an xml file that
> is formatted with google base xsd (http://base.google.com/base/base.xsd). I
> need a mapping schema for that, correct? To map the elements of the xml to
> my table. Does anyone have a mapping schema for this?
> Steve
>
>

Mapping schema for GoogleBase XSD

I want to load into a SQL Server table with SQLXMLBulkload an xml file that
is formatted with google base xsd (http://base.google.com/base/base.xsd). I
need a mapping schema for that, correct? To map the elements of the xml to
my table. Does anyone have a mapping schema for this?
SteveHi Steve
The mapping depends on the database tables to which you want to map it to.
Best regards
Michael
"Steve Mc" <stevemc@.zillow.com> wrote in message
news:OGEN6NOiHHA.5052@.TK2MSFTNGP05.phx.gbl...
>I want to load into a SQL Server table with SQLXMLBulkload an xml file that
>is formatted with google base xsd (http://base.google.com/base/base.xsd).
>I need a mapping schema for that, correct? To map the elements of the xml
>to my table. Does anyone have a mapping schema for this?
> Steve
>|||Hello,
Please take a look at http://msdn2.microsoft.com/en-us/library/ms172649.aspx
to understand how annotations work.
Let me know if you need further assistance.
Regards,
--
Monica Frintu
"Steve Mc" wrote:

> I want to load into a SQL Server table with SQLXMLBulkload an xml file tha
t
> is formatted with google base xsd (http://base.google.com/base/base.xsd).
I
> need a mapping schema for that, correct? To map the elements of the xml t
o
> my table. Does anyone have a mapping schema for this?
> Steve
>
>sql

Mapping Package Variables to a SQL Query in an OLEDB Source Component

Learning how to use SSIS...

I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)

I wanted to add additional clauses to the where clause.

The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.

The package variable is called [User::Date_BeginningYesterday]

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.

http://msdn2.microsoft.com/en-us/library/ms139904.aspx

The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.

...cordell...

Not sure what your problem is; but the solution is to create a variable to hold your query, let's say [User::SQLStatement] and use as value your query. Then set EvaluateAsExpression property of the variable to true. In the expression property, create an expression that will be evaluate at run time where you concatenate your query with the [User::Date_BeginningYesterday] variable. Back in your OLE DB Component you need to choose 'SQL Statement from variable' and then choose [User::SQLStatement] from the list.

Notice that you need to cast the value of [User::Date_BeginningYesterday] to string in the expression builder before concatenating its value to the sql statement.

Rafael Salas

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

|||

The question that I have is: Can one embed a package variable into a sql statement while selecting "SQL Statement" from the data access mode. If so how would would go about that?

...cordell...

|||

The short answer is no. you cannot reference a SSIS variable directly in your sql statement. You need to use '?' and then use the parameter mapping in your OLE DB source OR, to concatenate it within a second varibale as I described in the previous post.

Rafael Salas

sql

Mapping one table to another in diffirent databases

I want to map a table1 in one database to a table2 in another database. That way I can populate the table2 with the information that table1 has. How would you go about doing this. Very new at this and need help! Thanks alot .Do you mean that if an INSERT, UPDATE or DELETE Occurs you wan the action reflected in that table?|||Originally posted by Brett Kaiser
Do you mean that if an INSERT, UPDATE or DELETE Occurs you wan the action reflected in that table?

yeah, whenever something getts updated in one table in the database, the results will also reflect on the other table that lies it the other database. I am under the impression that yu have to map the databases together for that to happen. Is this something that is done with DTS? hopefully that makes a little more sence.|||Sounds like a job for triggers.|||Transactional replication can also be an answer.

Mapping multiple elements to the same table

I have a huge 1+GB xml file that I'd like to bulk load into a single staging
table in SQL Server. The xml file has subelements that need to be loaded
into the same table as the parent element. For example:
<Customer>
<Address>
</Address>
<ContactInfo>
</ContactInfo>
</Customer>
I cannot seem to get an XSD that will load all the data into 1 table. Also
because of the size of the XML file, performing an XSLT transformation on the
file is not possible. What are my options? Is it possible to map
subelements to the same table as the containing element?
Could you map them to two different views of the same table?
Note also, that the mapping kind of expects that you give a relationship
annotation when you map parents and children. What is the error message or
behaviour that you are getting?
Best regards
Michael
"Mark Weber" <Mark Weber@.discussions.microsoft.com> wrote in message
news:14D8B400-733C-4080-8888-BD3551C6AD09@.microsoft.com...
>I have a huge 1+GB xml file that I'd like to bulk load into a single
>staging
> table in SQL Server. The xml file has subelements that need to be loaded
> into the same table as the parent element. For example:
> <Customer>
> <Address>
> </Address>
> <ContactInfo>
> </ContactInfo>
> </Customer>
> I cannot seem to get an XSD that will load all the data into 1 table.
> Also
> because of the size of the XML file, performing an XSLT transformation on
> the
> file is not possible. What are my options? Is it possible to map
> subelements to the same table as the containing element?

Mapping multiple elements to the same table

I have a huge 1+GB xml file that I'd like to bulk load into a single staging
table in SQL Server. The xml file has subelements that need to be loaded
into the same table as the parent element. For example:
<Customer>
<Address>
</Address>
<ContactInfo>
</ContactInfo>
</Customer>
I cannot seem to get an XSD that will load all the data into 1 table. Also
because of the size of the XML file, performing an XSLT transformation on th
e
file is not possible. What are my options? Is it possible to map
subelements to the same table as the containing element?Could you map them to two different views of the same table?
Note also, that the mapping kind of expects that you give a relationship
annotation when you map parents and children. What is the error message or
behaviour that you are getting?
Best regards
Michael
"Mark Weber" <Mark Weber@.discussions.microsoft.com> wrote in message
news:14D8B400-733C-4080-8888-BD3551C6AD09@.microsoft.com...
>I have a huge 1+GB xml file that I'd like to bulk load into a single
>staging
> table in SQL Server. The xml file has subelements that need to be loaded
> into the same table as the parent element. For example:
> <Customer>
> <Address>
> </Address>
> <ContactInfo>
> </ContactInfo>
> </Customer>
> I cannot seem to get an XSD that will load all the data into 1 table.
> Also
> because of the size of the XML file, performing an XSLT transformation on
> the
> file is not possible. What are my options? Is it possible to map
> subelements to the same table as the containing element?sql

Mapping Elements to Database Fields in XSD for Bulkload

Hi Everyone,
Was wondering whether it is possible to map an element in a xml
document to a field in db table which has a different name.
A fragment of my Bulkload.xsd is below: Say I Changed my XML document
so that Event_ID was called EE_ID, could I still get EE_ID to map to
the Event_ID field in the database?
<xsd:element name="XML_WORKBOOKS" msch:relation="XML_WORKBOOKS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Event_ID" type="xsd:int" />
<xsd:element name="LastAuthor" type="xsd:string" />
<xsd:element name="Version" type="xsd:string" />
<xsd:element name="SaveComment" type="xsd:string" />
<xsd:element name="TM_ID" type="xsd:int" />
<xsd:element name="File_ID" type="xsd:int" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
Thanks for your help.
Yes, just use the field annotation.
e.g.
<xsd:element name="EE_ID" type="xsd:int" msch:field="Event_ID"/>
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"jnc" <j_culverwell@.yahoo.co.uk> wrote in message
news:44a3f856.0405091143.297ea942@.posting.google.c om...
> Hi Everyone,
> Was wondering whether it is possible to map an element in a xml
> document to a field in db table which has a different name.
> A fragment of my Bulkload.xsd is below: Say I Changed my XML document
> so that Event_ID was called EE_ID, could I still get EE_ID to map to
> the Event_ID field in the database?
>
> <xsd:element name="XML_WORKBOOKS" msch:relation="XML_WORKBOOKS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Event_ID" type="xsd:int" />
> <xsd:element name="LastAuthor" type="xsd:string" />
> <xsd:element name="Version" type="xsd:string" />
> <xsd:element name="SaveComment" type="xsd:string" />
> <xsd:element name="TM_ID" type="xsd:int" />
> <xsd:element name="File_ID" type="xsd:int" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> Thanks for your help.
|||Thanks, exactly what I was after.
Cheers....Jim
sql

mapping data types

I am building a .NET app that uses sqlDataReader.GetSchemaTable method. this
returns a table which includes the column "Provider Type" which is the
database data type of the column, however it is a numeric value, are the
mappings between these numeric values and the textual data type names
documented anywhere?
eg Money appears to be 9
and Date appears to be 15For SQL Server 2000, the systypes system table contains mappings between the
data type name and a number called 'xtype'. They don't seem to line up with
your observations though.
xtype Data type name
-- --
34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 sysname
231 nvarchar
239 nchar
"guy" wrote:

> I am building a .NET app that uses sqlDataReader.GetSchemaTable method. th
is
> returns a table which includes the column "Provider Type" which is the
> database data type of the column, however it is a numeric value, are the
> mappings between these numeric values and the textual data type names
> documented anywhere?
> eg Money appears to be 9
> and Date appears to be 15
>|||Mark,
hmmm interesting, so far i have found:-
2 bit
3 char
6 float
8 Int
9 money
15 smalldatetime
16 smallint
22 varchar
cheers
"Mark Williams" wrote:
> For SQL Server 2000, the systypes system table contains mappings between t
he
> data type name and a number called 'xtype'. They don't seem to line up wit
h
> your observations though.
> xtype Data type name
> -- --
> 34 image
> 35 text
> 36 uniqueidentifier
> 48 tinyint
> 52 smallint
> 56 int
> 58 smalldatetime
> 59 real
> 60 money
> 61 datetime
> 62 float
> 98 sql_variant
> 99 ntext
> 104 bit
> 106 decimal
> 108 numeric
> 122 smallmoney
> 127 bigint
> 165 varbinary
> 167 varchar
> 173 binary
> 175 char
> 189 timestamp
> 231 sysname
> 231 nvarchar
> 239 nchar
>
> "guy" wrote:
>

Mapping columns of complexType to different table

In my xml file I have an element called pointInTime which has 4 elements
associated with it.
Among these 4 fields, I need to map 1 field to one SQL table and remaining
fields to another table.
How do I write XSD schema for this.
<xs:element name="pointInTime" sql:relation="SpanFileDetailsTemp">
<xs:complexType>
<xs:sequence>
<xs:element ref="date" sql:field="FileDate"/>
<xs:element ref="isSetl"/>
<xs:element ref="setlQualifier"/>
<xs:element ref="clearingOrg"/>
</xs:sequence>
</xs:complexType>
</xs:element>
I tried to map 1 field to the table.
Now should I repeat this for other table?
But If I repeat this, the lement name PointInTime would be duplicated,
right?
So, should I use <xs:element ref="PointInTime" sql:relation="xxxx"> like
this ?
What is the difference between <xs:> and <xsd:>
I found both these while surfing about XSD's.
Regards
Meena
Meenakshi wrote:

> What is the difference between <xs:> and <xsd:>
> I found both these while surfing about XSD's.
Whether the prefix is xs or xsd or some other prefix (e.g. pf) does not
matter in terms of XML, what matters is the namespace URI bound to the
prefix. So you can use
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"></xs:schema>
or
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"></xsd:schema>
or
<pf:schema xmlns:pf="http://www.w3.org/2001/XMLSchema"></pf:schema>
or any other allowed prefix you like as long as the prefix is bound to
the namespace URI http://www.w3.org/2001/XMLSchema.

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Mapping columns of complexType to different table

In my xml file I have an element called pointInTime which has 4 elements
associated with it.
Among these 4 fields, I need to map 1 field to one SQL table and remaining
fields to another table.
How do I write XSD schema for this.
<xs:element name="pointInTime" sql:relation="SpanFileDetailsTemp">
<xs:complexType>
<xs:sequence>
<xs:element ref="date" sql:field="FileDate"/>
<xs:element ref="isSetl"/>
<xs:element ref="setlQualifier"/>
<xs:element ref="clearingOrg"/>
</xs:sequence>
</xs:complexType>
</xs:element>
I tried to map 1 field to the table.
Now should I repeat this for other table?
But If I repeat this, the lement name PointInTime would be duplicated,
right?
So, should I use <xs:element ref="PointInTime" sql:relation="xxxx"> like
this ?
What is the difference between <xs:> and <xsd:>
I found both these while surfing about XSD's.
Regards
MeenaMeenakshi wrote:

> What is the difference between <xs:> and <xsd:>
> I found both these while surfing about XSD's.
Whether the prefix is xs or xsd or some other prefix (e.g. pf) does not
matter in terms of XML, what matters is the namespace URI bound to the
prefix. So you can use
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"></xs:schema>
or
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"></xsd:schema>
or
<pf:schema xmlns:pf="http://www.w3.org/2001/XMLSchema"></pf:schema>
or any other allowed prefix you like as long as the prefix is bound to
the namespace URI http://www.w3.org/2001/XMLSchema.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/sql

Mapping Column Headers from Source to Rows in a spreadsheet

Hello,

I am trying to do the following:

I have been given an MS Access Database that has a table with columns

I have to create a spreadsheet that will have the data stored in the column header as a row (essentially we are creating a spreadsheet that records all of the different columns in all of the different tables in the MS Access DB).

Any suggestions?

Where the problem is?

Mapping a string field to Boolean output in SELECT clause

Hello,
I am facing a problem in a SELECT clause which i cannot solve.
In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not.
For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC".
when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:

SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3
FROM myTable

but this doesn't work in SQL 2005!
Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.

thanks a lot for your help!

Hi,

try this here:

SELECT Column1 AS Col1, Column2 AS Col2, CASE WHEN TypeColumn = "AAA" THEN 1 ELSE 0 END AS Col3
FROM myTable

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||It works!
Thank you, thank you, thank you!!!!!!!sql