How to read/select data from a text file in TLSQL

Hello All,
I wanna write a TSQl script which will read the data from a text file(having table like structure). Not with DTS, I wanna do this programatically as sql batch file. Kindly help me this is very urgent.
Thanks in advance
Thazul:)Why is dts not an option ? Please provide a sample your data from the text file - is it delimited or fixed ?|||Is it possible to read a "csv" file?

It is (as I've understood) via ODBC, but I guess ODBC won't help in this particular case?|||Originally posted by thazanm
Hello All,
I wanna write a TSQl script which will read the data from a text file(having table like structure). Not with DTS, I wanna do this programatically as sql batch file. Kindly help me this is very urgent.
Thanks in advance
Thazul:)

Thanks for ur reply.
I wanna transfer data offline, so i cant use DTS. I am doing this offline data transfer using bcp(getting data from source database and making files then read from files and put it into target database). This is helpfull for me when the target database is fresh, when the target databse is having data, it need some validation for duplication, parent child relationship based on identity columns.

All I want is, I am creating few file for each table using bcp with -c or
-n option. After creating this I wanna read the data from created files to do some validation in TSQL and then load into target database.

I mentioned offline mean, I am not sure what will be the target database and I am not sure about their database informations. This script has to go as a pre install script before our product get installed.

I wanna put this in a simple way. In oracle there is Pl/SQL package available to open, read and write data from files to table and vice versa like UTL_FILE.<procedures>. Is there any functions or procedures available in SQLServer TSQL.

Thanks in advance
Thazul|||You may have already mentioned why you can't do this - in situations like this I have a holding database and work with the data using tsql this way. Massage the data with a stored procedure and then output the data to the destination database/table.|||Alrigth, I am sorry for the improper information provided.
Well I am already having a TSQL script which will transfer data from one database to another database after doing all kind of validations. The isssue is doing it offline. I cant connect two database at the client place. Some client places the dont agree to connect with our database.
I this facility(reading from files having table like data) is available then I can go for complete batch file.
I really appricate ur suggestion, if anyone can provide me the syntax or comman, it will be great.

Thanks in advance,
Thazul|||Are you looking for a way to read in a file, run validations, then output the data to a file ?|||RE:The isssue is doing it offline. I cant connect two database at the client place. Some client places the dont agree to connect with our database.

In some situations one may attach an MDF file (pre-populated with the necessary data in tables), and subsequently use it as the staging area database for validation, transfer, etc. (to other DB targets). In this way the stageing area DB is effectively populated "off-line" (from a client perspective).|||Originally posted by rnealejr
Are you looking for a way to read in a file, run validations, then output the data to a file ?

Hi,
I am looking for reading the data from file and run validation and then store it into database tables.

Thanks in advance|||I am confused as to why you can't use dts within your tsql - but you can accomplish this task by using bcp within your tsql, have your tsql validate and spit it out to a table(s).
If this does not satisify your requirements, please let me know - a step by step detail of the entire process would be helpful- offline is a little vague at this point, since tsql implies online.

How to query children of a given node?

I'm new to SQLXML extenions and I'm curious if SQL Server
can handle what I need.
I need to store a tree data structure and I need to query
the children within a particular or multiple branches of
this tree. For example:
<Root>
<Branch Id=0,State=GA,Level=SpecialLevel/>
<Child Id=1,State=FL,Level=Grant/>
<Child Id=2,State=CA,Level=Denied/>
<Branch Id=3,State=WY,Level=NONE/>
<Child Id=4,State=TN,Level=Basic/>
<Root/>
My goal would like something like this I guess:
{
SELECT Id, State, Level
FROM RootTable
WHERE Root.Branch.Id = 0
}
I of course need to support the basics too:
- Create new tree
- Create new branch within existing tree
- Add/Update/Delete children and branches independent of
the other nodes
I can implement all of these features in C++ using a
standard DOM library. But, I'd love to get the powerful
querying capabilities of SQL along with the easy and
reliable admin functions.
I'm essentially hanging my decision to use a hand-coded
C++ XML server vs using SQL with XML extensions on the
querying capabilities that I need. From what I've read so
far, it seems pretty doable that SQLXML can handle the
insert, modify and delete operations. But, I'm not sure
about the querying capability.
Any help would be Greatly Appreciated :)!!
ChrisDepending on the shaping/filtering requirement you can either use XPath
against annotated schema or you can use FOR XML on the relational form (and
OpenXML or annotated schemas to store and update).
HTH
Michael
"ChrisD" <christopher_douglass@.deletethis.yahooDOTcom> wrote in message
news:1bce01c5056f$700cc140$a401280a@.phx.gbl...
> I'm new to SQLXML extenions and I'm curious if SQL Server
> can handle what I need.
> I need to store a tree data structure and I need to query
> the children within a particular or multiple branches of
> this tree. For example:
> <Root>
> <Branch Id=0,State=GA,Level=SpecialLevel/>
> <Child Id=1,State=FL,Level=Grant/>
> <Child Id=2,State=CA,Level=Denied/>
> <Branch Id=3,State=WY,Level=NONE/>
> <Child Id=4,State=TN,Level=Basic/>
> <Root/>
> My goal would like something like this I guess:
> {
> SELECT Id, State, Level
> FROM RootTable
> WHERE Root.Branch.Id = 0
> }
> I of course need to support the basics too:
> - Create new tree
> - Create new branch within existing tree
> - Add/Update/Delete children and branches independent of
> the other nodes
> I can implement all of these features in C++ using a
> standard DOM library. But, I'd love to get the powerful
> querying capabilities of SQL along with the easy and
> reliable admin functions.
> I'm essentially hanging my decision to use a hand-coded
> C++ XML server vs using SQL with XML extensions on the
> querying capabilities that I need. From what I've read so
> far, it seems pretty doable that SQLXML can handle the
> insert, modify and delete operations. But, I'm not sure
> about the querying capability.
> Any help would be Greatly Appreciated :)!!
> Chris|||Where do I find query examples using either or both
methods? Thanks!

>--Original Message--
>Depending on the shaping/filtering requirement you can
either use XPath
>against annotated schema or you can use FOR XML on the
relational form (and
>OpenXML or annotated schemas to store and update).
>HTH
>Michael
>"ChrisD" <christopher_douglass@.deletethis.yahooDOTcom>
wrote in message
>news:1bce01c5056f$700cc140$a401280a@.phx.gbl...
Server
query
so
>
>.
>|||Books Online (the SQL Server documentation) or www.sqlxml.org are two good
places to start.
Best regards
Michael
"Chris Douglass" <anonymous@.discussions.microsoft.com> wrote in message
news:1f4b01c5061f$19d99590$a401280a@.phx.gbl...
> Where do I find query examples using either or both
> methods? Thanks!
>
> either use XPath
> relational form (and
> wrote in message
> Server
> query
> so

How to query children of a given node?

I'm new to SQLXML extenions and I'm curious if SQL Server
can handle what I need.
I need to store a tree data structure and I need to query
the children within a particular or multiple branches of
this tree. For example:
<Root>
<Branch Id=0,State=GA,Level=SpecialLevel/>
<Child Id=1,State=FL,Level=Grant/>
<Child Id=2,State=CA,Level=Denied/>
<Branch Id=3,State=WY,Level=NONE/>
<Child Id=4,State=TN,Level=Basic/>
<Root/>
My goal would like something like this I guess:
{
SELECT Id, State, Level
FROM RootTable
WHERE Root.Branch.Id = 0
}
I of course need to support the basics too:
- Create new tree
- Create new branch within existing tree
- Add/Update/Delete children and branches independent of
the other nodes
I can implement all of these features in C++ using a
standard DOM library. But, I'd love to get the powerful
querying capabilities of SQL along with the easy and
reliable admin functions.
I'm essentially hanging my decision to use a hand-coded
C++ XML server vs using SQL with XML extensions on the
querying capabilities that I need. From what I've read so
far, it seems pretty doable that SQLXML can handle the
insert, modify and delete operations. But, I'm not sure
about the querying capability.
Any help would be Greatly Appreciated !!
Chris
Depending on the shaping/filtering requirement you can either use XPath
against annotated schema or you can use FOR XML on the relational form (and
OpenXML or annotated schemas to store and update).
HTH
Michael
"ChrisD" <christopher_douglass@.deletethis.yahooDOTcom> wrote in message
news:1bce01c5056f$700cc140$a401280a@.phx.gbl...
> I'm new to SQLXML extenions and I'm curious if SQL Server
> can handle what I need.
> I need to store a tree data structure and I need to query
> the children within a particular or multiple branches of
> this tree. For example:
> <Root>
> <Branch Id=0,State=GA,Level=SpecialLevel/>
> <Child Id=1,State=FL,Level=Grant/>
> <Child Id=2,State=CA,Level=Denied/>
> <Branch Id=3,State=WY,Level=NONE/>
> <Child Id=4,State=TN,Level=Basic/>
> <Root/>
> My goal would like something like this I guess:
> {
> SELECT Id, State, Level
> FROM RootTable
> WHERE Root.Branch.Id = 0
> }
> I of course need to support the basics too:
> - Create new tree
> - Create new branch within existing tree
> - Add/Update/Delete children and branches independent of
> the other nodes
> I can implement all of these features in C++ using a
> standard DOM library. But, I'd love to get the powerful
> querying capabilities of SQL along with the easy and
> reliable admin functions.
> I'm essentially hanging my decision to use a hand-coded
> C++ XML server vs using SQL with XML extensions on the
> querying capabilities that I need. From what I've read so
> far, it seems pretty doable that SQLXML can handle the
> insert, modify and delete operations. But, I'm not sure
> about the querying capability.
> Any help would be Greatly Appreciated !!
> Chris
|||Where do I find query examples using either or both
methods? Thanks!

>--Original Message--
>Depending on the shaping/filtering requirement you can
either use XPath
>against annotated schema or you can use FOR XML on the
relational form (and
>OpenXML or annotated schemas to store and update).
>HTH
>Michael
>"ChrisD" <christopher_douglass@.deletethis.yahooDOTcom>
wrote in message[vbcol=seagreen]
>news:1bce01c5056f$700cc140$a401280a@.phx.gbl...
Server[vbcol=seagreen]
query[vbcol=seagreen]
so
>
>.
>
|||Books Online (the SQL Server documentation) or www.sqlxml.org are two good
places to start.
Best regards
Michael
"Chris Douglass" <anonymous@.discussions.microsoft.com> wrote in message
news:1f4b01c5061f$19d99590$a401280a@.phx.gbl...[vbcol=seagreen]
> Where do I find query examples using either or both
> methods? Thanks!
> either use XPath
> relational form (and
> wrote in message
> Server
> query
> so