Doubt

Hi there

I have a question here. I need to import the excel template into sql server data base tables. I did worked on the regular importing of excel spread sheets to import into the appropriate data base columns. I am not getting idea with the eacel template. My main goal is to import the excel template to sql tables and then reproduce the same template for the user in the browser. If any body can help i would really appreciate....Welcome to the forums..looks like you signed up just a few mins back.

can you xplain your q in a more detailed way.
>>I am not getting idea with the eacel template.
Did you mean you are not able to use the DTS Wizard to import the excel file into a sql table ?

>> My main goal is to import the excel template to sql tables and then reproduce the same template for the user in the browser.

I didnt understand this part either.|||Yes I can't use the regular DTS ti import into the data Base tables. Because the the excel templates might be in any order and they have data in any way. I have a sample application with title, genere, format size , frequency, attributes and insertion costs as columns in excel template. The data can come or can't come in exact order as in regular excel spread sheets. I have to read the data cell by cell and import into the data base. For that I can't use the Microsoft Jet provider . I can use the Jet for the regular spread sheets, but not for the templates. The template where I import the data into the data base, I need to make a look up for each and every tables.(ex: Market--country--> string look up int fk to the country table, Devision: string look up fk to a devision table) and at the end I need to display the information to the end user according to his needs. So please tell me any ideas how I can make a look up and how I can read the data from excel template and finally import into sql tables.|||you can prbly write a stored proc that will query from a table . you can import your excel sheet into an sql table. use that as a container..then write a select stmt to query against that table. assuming the column names will be the same no matter what order they are in, this approach might work. IF the column names are also different then it will require a human to read through to identify the columns.|||Do you have any idea about the look ups. How to design the data base for that excel template. Here is I got some sentences...

User uploads an excel sheet template that has been filled out.

Header:

Market -- country string lookup int fk to a country table
Business Group -- division string lookup fk to division table
ALLOCATED BUDGET: float
GRPs (IF APPLICABLE): float
% REACH (IF APPLICABLE): float
OTS (IF APPLICABLE): float
start date smalldatetime
end date smalldatetime
flight type char[1]

row data:

Title string lookup int fk to a media location table
Frequency -- you can ignore insert as it is driven by title
Client cost float
Insertion cost float
Genre string lookup fk to Genre table
Start date smalldatetime
end date smalldatetime
Booking Date smalldatetime
Material Date smalldatetime
Onsale Date smalldatetime
Attrb 1 -- 10 varchars any number of attributes from 0 through 10 posibblities based on flight type (ie circulation format/size) which are
fk to title table

Iam not understanding the what the header is and what the row data is...How sould I make the look ups...Thanks for you answers.|||(1) Market -- country string lookup int fk to a country table
can you xplain what this means.

(2) are you trying to this thing programmatically or manually.|||Iam also understanding that much. If you can explain me what's the look up and how to make a look up in the data base?. What kind of queries you write to get the thing I want that would be great.....

Market -- country string lookup int fk to a country table

I feel if suppose the country is India and country ID is 15, whenever I should enter 15 I need to get India. i think this is the look up...If you knowsome thing about how to make look ups please explain me....

Doing a join between XML data typed field and a relational table in Yukon (SQL 2005)

Hello,
I have a Yukon XML question and would be very happy if someone can
assist:
Let's assume I have 2 tables:
One will be product table Regular relational table with Product ID
as a primary key and some descriptive fields.
Second is an Order table it has Order ID as a PK and Order details
which is a field of XML data type and holds all lines in the order
including quantity, product id, prices, etc.
I want to make a join that will present the orders with all product
details for each order line.
Do you know how can I do a Join between XML field and relational
table?
Thanks,
Nitsan
"Nitsan" <nitsan.shaked@.intel.com> wrote in message
news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
[snip]
> Do you know how can I do a Join between XML field and relational
> table?
You should be able to do this with the new nesting of FOR XML statements in
Yukon. See:
http://sqljunkies.com/WebLog/mrys/ar...2/19/1208.aspx
Bryant
|||Thanks for your answer,
I looked into it and it looks like it was doing select on relational tables
and just created XML as an answer.
I am looking for the opposite - Query in XML content join relational table
to get data (even not necessarily in XML format).
Thanks,
Nitsan
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:OeRsQuBaEHA.524@.TK2MSFTNGP09.phx.gbl...
> "Nitsan" <nitsan.shaked@.intel.com> wrote in message
> news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
> [snip]
> You should be able to do this with the new nesting of FOR XML statements
in
> Yukon. See:
> http://sqljunkies.com/WebLog/mrys/ar...2/19/1208.aspx
> --
> Bryant
>
|||Even outside of the capabilties of FOR XML, you can do what you want.
You can use a value from the XML data type using the method xmltype.value()
in a SQL predicate. Or you can use the value of a sql:variable or sql:column
to do the join inside an XQuery.
Bob Beauchemin
http://staff.develop.com/bobb
"Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
news:ccuhjj$kr2$1@.news01.intel.com...
> Thanks for your answer,
> I looked into it and it looks like it was doing select on relational
tables
> and just created XML as an answer.
> I am looking for the opposite - Query in XML content join relational table
> to get data (even not necessarily in XML format).
> Thanks,
> Nitsan
> "Bryant Likes" <bryant@.suespammers.org> wrote in message
> news:OeRsQuBaEHA.524@.TK2MSFTNGP09.phx.gbl...
> in
>
|||Nitsan,
in SQL Server 2005 Beta 2 or SQL Server 2005 Express Edition Technical
Preview you can use nodes() method on XML type to get a row set out of an
XML instance and then do the join in T-SQL space:
create table products(ProdID int primary key, ProdDesc nvarchar(max))
go
create table orders(OrderID int primary key, OrdDetails xml)
go
insert products values(1, 'Prod1')
insert products values(2, 'Prod2')
go
insert orders values(1,
'<prod id="1">
<quantity>2</quantity>
<price>12345.67</price>
</prod>
<prod id="2">
<quantity>3</quantity>
<price>9876.54</price>
</prod>')
go
select OrderID, quantity, price, ProdDesc
from
(
select
OrderID,
prod.value('./@.id', 'int') as id,
prod.value('(./quantity)[1]', 'int') as quantity,
prod.value('(./price)[1]', 'money') as price
from orders cross apply orders.OrdDetails.nodes('/prod') as prods(prod)
) as OrdLine
join products on OrdLine.id=products.ProdID
go
You can then apply FOR XML to results if you need results as XML.
Regards,
Eugene Kogan,
Technical Lead,
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Nitsan" <nitsan.shaked@.intel.com> wrote in message
news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
> Hello,
> I have a Yukon XML question and would be very happy if someone can
> assist:
> Let's assume I have 2 tables:
> One will be product table - Regular relational table with Product ID
> as a primary key and some descriptive fields.
> Second is an Order table - it has Order ID as a PK and Order details
> which is a field of XML data type and holds all lines in the order
> including quantity, product id, prices, etc.
> I want to make a join that will present the orders with all product
> details for each order line.
> Do you know how can I do a Join between XML field and relational
> table?
> Thanks,
> Nitsan
|||Thanks,
Do you have a short example if usin git for this purpose?
"Bob Beauchemin" <no_bobb_spam@.develop.com> wrote in message
news:O9D73DEaEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Even outside of the capabilties of FOR XML, you can do what you want.
> You can use a value from the XML data type using the method
xmltype.value()
> in a SQL predicate. Or you can use the value of a sql:variable or
sql:column[vbcol=seagreen]
> to do the join inside an XQuery.
> Bob Beauchemin
> http://staff.develop.com/bobb
>
> "Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
> news:ccuhjj$kr2$1@.news01.intel.com...
> tables
table[vbcol=seagreen]
statements
>
|||Thank you very much !!!
"Eugene Kogan" <eugene_kogan@.hotmail.com> wrote in message
news:uUd0kAJaEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Nitsan,
> in SQL Server 2005 Beta 2 or SQL Server 2005 Express Edition Technical
> Preview you can use nodes() method on XML type to get a row set out of an
> XML instance and then do the join in T-SQL space:
> create table products(ProdID int primary key, ProdDesc nvarchar(max))
> go
> create table orders(OrderID int primary key, OrdDetails xml)
> go
> insert products values(1, 'Prod1')
> insert products values(2, 'Prod2')
> go
> insert orders values(1,
> '<prod id="1">
> <quantity>2</quantity>
> <price>12345.67</price>
> </prod>
> <prod id="2">
> <quantity>3</quantity>
> <price>9876.54</price>
> </prod>')
> go
> select OrderID, quantity, price, ProdDesc
> from
> (
> select
> OrderID,
> prod.value('./@.id', 'int') as id,
> prod.value('(./quantity)[1]', 'int') as quantity,
> prod.value('(./price)[1]', 'money') as price
> from orders cross apply orders.OrdDetails.nodes('/prod') as prods(prod)
> ) as OrdLine
> join products on OrdLine.id=products.ProdID
> go
> You can then apply FOR XML to results if you need results as XML.
>
> Regards,
> Eugene Kogan,
> Technical Lead,
> SQL Server Engine
> Microsoft Corp
> [This posting is provided "AS IS" with no warranties, and confers no
> rights.]
> "Nitsan" <nitsan.shaked@.intel.com> wrote in message
> news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
>
|||Hi Nitsan,
Here's a really short example, starting with the authors table in the pubs
database:
create table xmlauth (
authbio xml)
go
insert xmlauth values('<author id="172-32-1176"><hobbies><golf score="78"
/><tennis/></hobbies></author>')
insert xmlauth values('<author id="213-46-8915"><hobbies><golf score="82"
/><bowling/></hobbies></author>')
If you know each document contains one author, you can use xml.value:
select authbio.query('/author/hobbies/*') as hobbies, au_lname
from authors, xmlauth
where authors.au_id =
xmlauth.authbio.value('author[1]/@.id', 'varchar(11)')
(if you have a schema collection which specifies a single author occurance,
you can leave out the subscript [1] in the value subquery)
If documents may contain more than one author, (e.g)
insert xmlauth values('
<authors>
<author id="274-80-9391"><hobbies><swimming
/><bowling/></hobbies></author>
<author id="274-80-9391"><hobbies><baseball
/><bowling/></hobbies></author>
</authors>')
you would need xml.nodes. Eugene's excellent example illustrates that.
You can even do this (caution: NOT recommended, cartesian product) using the
SQL value in the XQuery:
select * from
(
select authbio.query('
for $a in /author
where $a/@.id = sql:column("authors.au_id")
return $a/hobbies/*') as hobbies, au_lname
from xmlauth, authors
) as a
where hobbies.exist('/*')=1
or
select * from
(
select authbio.query('
for $a in /author
where $a/@.id = sql:column("authors.au_id")
return $a/hobbies/*') as hobbies, au_lname
from xmlauth, authors
) as a
where convert(nvarchar(100), hobbies) <> ''
Hope this helps,
Bob Beauchemin
http://staff.develop.com/bobb
"Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
news:cd034u$fo8$1@.news01.intel.com...
> Thanks,
> Do you have a short example if usin git for this purpose?
> "Bob Beauchemin" <no_bobb_spam@.develop.com> wrote in message
> news:O9D73DEaEHA.3508@.TK2MSFTNGP09.phx.gbl...
> xmltype.value()
> sql:column
> table
> statements
>