Hello All!
I would like to thank everyone for all the help, but.. (there is always a
but) i have another question.
I would like in my select a column displaying if the current line is a
Company or a Person, something like that:
SELECT *, "(Company or Person) As Type" FROM Client
LEFT JOIN Person ON Client.ID = Person.ID
LEFT JOIN Company ON Client.ID = Company.ID
I anyone could help me, i raelly would appreciate it!
thanks,
Bruno N
CREATE TABLE [Person] (
[Id] [int] NOT NULL ,
[RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Person_Customer] FOREIGN KEY
(
[Id]
) REFERENCES [Customer] (
[Id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
CREATE TABLE [Customer] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [Company] (
[Id] [int] NOT NULL ,
[CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Company_Customer] FOREIGN KEY
(
[Id]
) REFERENCES [Customer] (
[Id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]SELECT *,
CASE
WHEN Client.ID = Person.ID THEN 'Person'
WHEN Company.ID = Person.ID THEN 'Company'
ELSE ''
END
FROM
...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bruno N" <nylren@.hotmail.com> wrote in message
news:u31HkjMJFHA.3136@.TK2MSFTNGP15.phx.gbl...
> Hello All!
> I would like to thank everyone for all the help, but.. (there is always a
> but) i have another question.
> I would like in my select a column displaying if the current line is a
> Company or a Person, something like that:
>
> SELECT *, "(Company or Person) As Type" FROM Client
> LEFT JOIN Person ON Client.ID = Person.ID
> LEFT JOIN Company ON Client.ID = Company.ID
>
> I anyone could help me, i raelly would appreciate it!
> thanks,
> Bruno N
>
> CREATE TABLE [Person] (
> [Id] [int] NOT NULL ,
> [RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Person_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
> CREATE TABLE [Customer] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [Company] (
> [Id] [int] NOT NULL ,
> [CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Company_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>|||SELECT *,
Case When Person.ID Is Not Null then 'Person'
When Company.ID Is Not Null then 'Company'
When Customer.ID Is Not Null then 'Customer' ENd As Type
FROM Client
LEFT JOIN Person ON Client.ID = Person.ID
LEFT JOIN Company ON Client.ID = Company.ID
"Bruno N" wrote:
> Hello All!
> I would like to thank everyone for all the help, but.. (there is always a
> but) i have another question.
> I would like in my select a column displaying if the current line is a
> Company or a Person, something like that:
>
> SELECT *, "(Company or Person) As Type" FROM Client
> LEFT JOIN Person ON Client.ID = Person.ID
> LEFT JOIN Company ON Client.ID = Company.ID
>
> I anyone could help me, i raelly would appreciate it!
> thanks,
> Bruno N
>
> CREATE TABLE [Person] (
> [Id] [int] NOT NULL ,
> [RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Person_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
> CREATE TABLE [Customer] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [Company] (
> [Id] [int] NOT NULL ,
> [CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Company_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
>|||SELECT ...,
CASE
WHEN Person.ID IS NOT NULL THEN 'Person'
WHEN Company.ID IS NOT NULL THEN 'Company'
END, ...
You seem to be missing the alternate key on Customer Name. IDENTITY
should never be the only key of a table. Also, your constraints don't
prevent the same entity being entered as both Cutsomer and Company.
David Portas
SQL Server MVP
--
Showing posts with label alli. Show all posts
Showing posts with label alli. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Retrieving XML from SQL SERVER
Hi All
I have various tables that have the following hierarchy
TABLE A
TABLE B
TABLE C
TABLE D
TABLE E
TABLE F.
Each of the tables has the right keys to make up the hierarchy, and
each child has one more field than the parent to make the connection.
I would like to pass a parameter to the TABLE A and that should give
back to me the whole xml.I want to call this procedure from an asp.
What are my options '
Can I use a huge template file for representing the whole hierarchy '
Please suggest.
Thanks for your time and patience.
Satish
KARRYS@.Gmail.comIn SQL Server 2000 your options are:
XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
component) or writing FOR XML EXPLICIT queries (also lovingly called the
"query from hell" :-)).
In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
EXPLICIT mode).
Best regards
Michael
"Satish KARRY" <karrys@.gmail.com> wrote in message
news:1107881623.352032.259820@.g14g2000cwa.googlegroups.com...
> Hi All
> I have various tables that have the following hierarchy
> TABLE A
> TABLE B
> TABLE C
> TABLE D
> TABLE E
> TABLE F.
> Each of the tables has the right keys to make up the hierarchy, and
> each child has one more field than the parent to make the connection.
> I would like to pass a parameter to the TABLE A and that should give
> back to me the whole xml.I want to call this procedure from an asp.
> What are my options '
> Can I use a huge template file for representing the whole hierarchy '
> Please suggest.
> Thanks for your time and patience.
> Satish
> KARRYS@.Gmail.com
>|||Hello Michael,
We are exposed to the same challenge, but we are required to use XML
EXPLICIT (caused by political issues)
The only samples I can find are using Customers, Orders & Orderdetails but
not with different types of child elements connected to the same parent
node.
Could you post an example of a query using XML EXPLICIT for the table
structure specified by Satish?
Thanks,
Peter Vervoorn
(if you want to reply directly to me, replace newsuser with my first name)
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2000 your options are:
> XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
> component) or writing FOR XML EXPLICIT queries (also lovingly called the
> "query from hell" :-)).
> In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
> EXPLICIT mode).
> Best regards
> Michael
> "Satish KARRY" <karrys@.gmail.com> wrote in message
> news:1107881623.352032.259820@.g14g2000cwa.googlegroups.com...
>|||You may want to look at the SQL Server 2005 whitepaper. It contains FOR XML
EXPLICIT queries that do
TABLE A
TABLE B
TABLE C
The URL is:
http://msdn.microsoft.com/XML/Build...l/forxml2k5.asp
Does that help?
Best regards
Michael
"Peter Vervoorn" <newsuser@.vervoorn.com> wrote in message
news:420b640e$0$28986$e4fe514c@.news.xs4all.nl...
> Hello Michael,
> We are exposed to the same challenge, but we are required to use XML
> EXPLICIT (caused by political issues)
> The only samples I can find are using Customers, Orders & Orderdetails but
> not with different types of child elements connected to the same parent
> node.
> Could you post an example of a query using XML EXPLICIT for the table
> structure specified by Satish?
> Thanks,
> Peter Vervoorn
> (if you want to reply directly to me, replace newsuser with my first name)
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>|||Hi Mike
Would you have an example to the XSD and asp combination in particular.
I am working on sql server 2000 and asp and not asp.net
Thanks for your help.
Sincerely
Satish|||The documentation and whitepapers on the SQLXML 3.0 component on MSDN or
www.sqlxml.org should have some information to get you started. with
annotated schemas
Best regards
Michael
"A" <karrys@.gmail.com> wrote in message
news:1108990971.486858.152420@.l41g2000cwc.googlegroups.com...
> Hi Mike
> Would you have an example to the XSD and asp combination in particular.
> I am working on sql server 2000 and asp and not asp.net
> Thanks for your help.
> Sincerely
> Satish
>|||Hello Michael,
Sorry it took so long, last w
I was ill.
The structure shown in the document is what I tried.
For some reason we are now allowed to assemble the XML outside sql, so it's
no issue anymore
Thanks anyway,
Peter
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umZfIvMEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You may want to look at the SQL Server 2005 whitepaper. It contains FOR
> XML EXPLICIT queries that do
> TABLE A
> TABLE B
> TABLE C
> The URL is:
> http://msdn.microsoft.com/XML/Build...l/forxml2k5.asp
> Does that help?
> Best regards
> Michael
I have various tables that have the following hierarchy
TABLE A
TABLE B
TABLE C
TABLE D
TABLE E
TABLE F.
Each of the tables has the right keys to make up the hierarchy, and
each child has one more field than the parent to make the connection.
I would like to pass a parameter to the TABLE A and that should give
back to me the whole xml.I want to call this procedure from an asp.
What are my options '
Can I use a huge template file for representing the whole hierarchy '
Please suggest.
Thanks for your time and patience.
Satish
KARRYS@.Gmail.comIn SQL Server 2000 your options are:
XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
component) or writing FOR XML EXPLICIT queries (also lovingly called the
"query from hell" :-)).
In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
EXPLICIT mode).
Best regards
Michael
"Satish KARRY" <karrys@.gmail.com> wrote in message
news:1107881623.352032.259820@.g14g2000cwa.googlegroups.com...
> Hi All
> I have various tables that have the following hierarchy
> TABLE A
> TABLE B
> TABLE C
> TABLE D
> TABLE E
> TABLE F.
> Each of the tables has the right keys to make up the hierarchy, and
> each child has one more field than the parent to make the connection.
> I would like to pass a parameter to the TABLE A and that should give
> back to me the whole xml.I want to call this procedure from an asp.
> What are my options '
> Can I use a huge template file for representing the whole hierarchy '
> Please suggest.
> Thanks for your time and patience.
> Satish
> KARRYS@.Gmail.com
>|||Hello Michael,
We are exposed to the same challenge, but we are required to use XML
EXPLICIT (caused by political issues)
The only samples I can find are using Customers, Orders & Orderdetails but
not with different types of child elements connected to the same parent
node.
Could you post an example of a query using XML EXPLICIT for the table
structure specified by Satish?
Thanks,
Peter Vervoorn
(if you want to reply directly to me, replace newsuser with my first name)
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2000 your options are:
> XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
> component) or writing FOR XML EXPLICIT queries (also lovingly called the
> "query from hell" :-)).
> In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
> EXPLICIT mode).
> Best regards
> Michael
> "Satish KARRY" <karrys@.gmail.com> wrote in message
> news:1107881623.352032.259820@.g14g2000cwa.googlegroups.com...
>|||You may want to look at the SQL Server 2005 whitepaper. It contains FOR XML
EXPLICIT queries that do
TABLE A
TABLE B
TABLE C
The URL is:
http://msdn.microsoft.com/XML/Build...l/forxml2k5.asp
Does that help?
Best regards
Michael
"Peter Vervoorn" <newsuser@.vervoorn.com> wrote in message
news:420b640e$0$28986$e4fe514c@.news.xs4all.nl...
> Hello Michael,
> We are exposed to the same challenge, but we are required to use XML
> EXPLICIT (caused by political issues)
> The only samples I can find are using Customers, Orders & Orderdetails but
> not with different types of child elements connected to the same parent
> node.
> Could you post an example of a query using XML EXPLICIT for the table
> structure specified by Satish?
> Thanks,
> Peter Vervoorn
> (if you want to reply directly to me, replace newsuser with my first name)
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>|||Hi Mike
Would you have an example to the XSD and asp combination in particular.
I am working on sql server 2000 and asp and not asp.net
Thanks for your help.
Sincerely
Satish|||The documentation and whitepapers on the SQLXML 3.0 component on MSDN or
www.sqlxml.org should have some information to get you started. with
annotated schemas
Best regards
Michael
"A" <karrys@.gmail.com> wrote in message
news:1108990971.486858.152420@.l41g2000cwc.googlegroups.com...
> Hi Mike
> Would you have an example to the XSD and asp combination in particular.
> I am working on sql server 2000 and asp and not asp.net
> Thanks for your help.
> Sincerely
> Satish
>|||Hello Michael,
Sorry it took so long, last w
The structure shown in the document is what I tried.
For some reason we are now allowed to assemble the XML outside sql, so it's
no issue anymore
Thanks anyway,
Peter
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umZfIvMEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You may want to look at the SQL Server 2005 whitepaper. It contains FOR
> XML EXPLICIT queries that do
> TABLE A
> TABLE B
> TABLE C
> The URL is:
> http://msdn.microsoft.com/XML/Build...l/forxml2k5.asp
> Does that help?
> Best regards
> Michael
Retrieving XML from SQL SERVER
Hi All
I have various tables that have the following hierarchy
TABLE A
TABLE B
TABLE C
TABLE D
TABLE E
TABLE F.
Each of the tables has the right keys to make up the hierarchy, and
each child has one more field than the parent to make the connection.
I would like to pass a parameter to the TABLE A and that should give
back to me the whole xml.I want to call this procedure from an asp.
What are my options ?
Can I use a huge template file for representing the whole hierarchy ?
Please suggest.
Thanks for your time and patience.
Satish
KARRYS@.Gmail.com
In SQL Server 2000 your options are:
XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
component) or writing FOR XML EXPLICIT queries (also lovingly called the
"query from hell" :-)).
In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
EXPLICIT mode).
Best regards
Michael
"Satish KARRY" <karrys@.gmail.com> wrote in message
news:1107881623.352032.259820@.g14g2000cwa.googlegr oups.com...
> Hi All
> I have various tables that have the following hierarchy
> TABLE A
> TABLE B
> TABLE C
> TABLE D
> TABLE E
> TABLE F.
> Each of the tables has the right keys to make up the hierarchy, and
> each child has one more field than the parent to make the connection.
> I would like to pass a parameter to the TABLE A and that should give
> back to me the whole xml.I want to call this procedure from an asp.
> What are my options ?
> Can I use a huge template file for representing the whole hierarchy ?
> Please suggest.
> Thanks for your time and patience.
> Satish
> KARRYS@.Gmail.com
>
|||Hello Michael,
We are exposed to the same challenge, but we are required to use XML
EXPLICIT (caused by political issues)
The only samples I can find are using Customers, Orders & Orderdetails but
not with different types of child elements connected to the same parent
node.
Could you post an example of a query using XML EXPLICIT for the table
structure specified by Satish?
Thanks,
Peter Vervoorn
(if you want to reply directly to me, replace newsuser with my first name)
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2000 your options are:
> XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
> component) or writing FOR XML EXPLICIT queries (also lovingly called the
> "query from hell" :-)).
> In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
> EXPLICIT mode).
> Best regards
> Michael
> "Satish KARRY" <karrys@.gmail.com> wrote in message
> news:1107881623.352032.259820@.g14g2000cwa.googlegr oups.com...
>
|||You may want to look at the SQL Server 2005 whitepaper. It contains FOR XML
EXPLICIT queries that do
TABLE A
TABLE B
TABLE C
The URL is:
http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp
Does that help?
Best regards
Michael
"Peter Vervoorn" <newsuser@.vervoorn.com> wrote in message
news:420b640e$0$28986$e4fe514c@.news.xs4all.nl...
> Hello Michael,
> We are exposed to the same challenge, but we are required to use XML
> EXPLICIT (caused by political issues)
> The only samples I can find are using Customers, Orders & Orderdetails but
> not with different types of child elements connected to the same parent
> node.
> Could you post an example of a query using XML EXPLICIT for the table
> structure specified by Satish?
> Thanks,
> Peter Vervoorn
> (if you want to reply directly to me, replace newsuser with my first name)
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>
|||Hi Mike
Would you have an example to the XSD and asp combination in particular.
I am working on sql server 2000 and asp and not asp.net
Thanks for your help.
Sincerely
Satish
|||The documentation and whitepapers on the SQLXML 3.0 component on MSDN or
www.sqlxml.org should have some information to get you started. with
annotated schemas
Best regards
Michael
"A" <karrys@.gmail.com> wrote in message
news:1108990971.486858.152420@.l41g2000cwc.googlegr oups.com...
> Hi Mike
> Would you have an example to the XSD and asp combination in particular.
> I am working on sql server 2000 and asp and not asp.net
> Thanks for your help.
> Sincerely
> Satish
>
|||Hello Michael,
Sorry it took so long, last week I was ill.
The structure shown in the document is what I tried.
For some reason we are now allowed to assemble the XML outside sql, so it's
no issue anymore
Thanks anyway,
Peter
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umZfIvMEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You may want to look at the SQL Server 2005 whitepaper. It contains FOR
> XML EXPLICIT queries that do
> TABLE A
> TABLE B
> TABLE C
> The URL is:
> http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp
> Does that help?
> Best regards
> Michael
I have various tables that have the following hierarchy
TABLE A
TABLE B
TABLE C
TABLE D
TABLE E
TABLE F.
Each of the tables has the right keys to make up the hierarchy, and
each child has one more field than the parent to make the connection.
I would like to pass a parameter to the TABLE A and that should give
back to me the whole xml.I want to call this procedure from an asp.
What are my options ?
Can I use a huge template file for representing the whole hierarchy ?
Please suggest.
Thanks for your time and patience.
Satish
KARRYS@.Gmail.com
In SQL Server 2000 your options are:
XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
component) or writing FOR XML EXPLICIT queries (also lovingly called the
"query from hell" :-)).
In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
EXPLICIT mode).
Best regards
Michael
"Satish KARRY" <karrys@.gmail.com> wrote in message
news:1107881623.352032.259820@.g14g2000cwa.googlegr oups.com...
> Hi All
> I have various tables that have the following hierarchy
> TABLE A
> TABLE B
> TABLE C
> TABLE D
> TABLE E
> TABLE F.
> Each of the tables has the right keys to make up the hierarchy, and
> each child has one more field than the parent to make the connection.
> I would like to pass a parameter to the TABLE A and that should give
> back to me the whole xml.I want to call this procedure from an asp.
> What are my options ?
> Can I use a huge template file for representing the whole hierarchy ?
> Please suggest.
> Thanks for your time and patience.
> Satish
> KARRYS@.Gmail.com
>
|||Hello Michael,
We are exposed to the same challenge, but we are required to use XML
EXPLICIT (caused by political issues)
The only samples I can find are using Customers, Orders & Orderdetails but
not with different types of child elements connected to the same parent
node.
Could you post an example of a query using XML EXPLICIT for the table
structure specified by Satish?
Thanks,
Peter Vervoorn
(if you want to reply directly to me, replace newsuser with my first name)
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2000 your options are:
> XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
> component) or writing FOR XML EXPLICIT queries (also lovingly called the
> "query from hell" :-)).
> In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
> EXPLICIT mode).
> Best regards
> Michael
> "Satish KARRY" <karrys@.gmail.com> wrote in message
> news:1107881623.352032.259820@.g14g2000cwa.googlegr oups.com...
>
|||You may want to look at the SQL Server 2005 whitepaper. It contains FOR XML
EXPLICIT queries that do
TABLE A
TABLE B
TABLE C
The URL is:
http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp
Does that help?
Best regards
Michael
"Peter Vervoorn" <newsuser@.vervoorn.com> wrote in message
news:420b640e$0$28986$e4fe514c@.news.xs4all.nl...
> Hello Michael,
> We are exposed to the same challenge, but we are required to use XML
> EXPLICIT (caused by political issues)
> The only samples I can find are using Customers, Orders & Orderdetails but
> not with different types of child elements connected to the same parent
> node.
> Could you post an example of a query using XML EXPLICIT for the table
> structure specified by Satish?
> Thanks,
> Peter Vervoorn
> (if you want to reply directly to me, replace newsuser with my first name)
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>
|||Hi Mike
Would you have an example to the XSD and asp combination in particular.
I am working on sql server 2000 and asp and not asp.net
Thanks for your help.
Sincerely
Satish
|||The documentation and whitepapers on the SQLXML 3.0 component on MSDN or
www.sqlxml.org should have some information to get you started. with
annotated schemas
Best regards
Michael
"A" <karrys@.gmail.com> wrote in message
news:1108990971.486858.152420@.l41g2000cwc.googlegr oups.com...
> Hi Mike
> Would you have an example to the XSD and asp combination in particular.
> I am working on sql server 2000 and asp and not asp.net
> Thanks for your help.
> Sincerely
> Satish
>
|||Hello Michael,
Sorry it took so long, last week I was ill.
The structure shown in the document is what I tried.
For some reason we are now allowed to assemble the XML outside sql, so it's
no issue anymore
Thanks anyway,
Peter
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umZfIvMEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You may want to look at the SQL Server 2005 whitepaper. It contains FOR
> XML EXPLICIT queries that do
> TABLE A
> TABLE B
> TABLE C
> The URL is:
> http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp
> Does that help?
> Best regards
> Michael
Friday, March 9, 2012
Retrieve windows user account
Hi, All
I'm use SQL Server 2005 and want to know is any way can retrieve windows
user account?
I want to retrieve windows user account and insert into the table.
I don't want to use any program lanaguage just want to know SQL 2005 has any
kind of function
or store procedure can support this.
Please give me function or store procedure name and reference.
Thanks for any advice!
Angiangi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565984cd-60c6-4df7-83ea-
2349b838ccb2.htm
"angi" <angi@.news.microsoft.com> wrote in message
news:OgEkdW0xGHA.2400@.TK2MSFTNGP03.phx.gbl...
> Hi, All
> I'm use SQL Server 2005 and want to know is any way can retrieve windows
> user account?
> I want to retrieve windows user account and insert into the table.
> I don't want to use any program lanaguage just want to know SQL 2005 has
> any kind of function
> or store procedure can support this.
> Please give me function or store procedure name and reference.
> Thanks for any advice!
> Angi
>|||Thanks for Uri.
But it my fault that didn't describe it clearly.
SYSTEM_USER retrive the windows user account who login the SQL Server.
But what I want is not who login to SQL Server and retrive it.
I want is retrive all of windows user account or user name on local server,
on windows server.
Example:
There are 10 windows user accounts on Local Server, such as John, Mary, May
and so on.
They are window server's user and maybe just John has the right to access
SQL Server.
And I want to use some kinds of SP or Function to get John, Mary, May's user
name
(not just login SQL Server's user, not SYSTEM_USER just show
"LOCALHOST\John"),
or some function like LDAP can get Windows user account or user name through
the T-SQL to retrive all of the windows user account or user name.
Such AS:
SELECT xxFn ... AS Username
EXEC xxSp ... AS Username
Username
----
John
Mary
May <-- user's account on windows server (not who login sql server)
...
uh... hope someone know what is my requirement!
Thanks for help!
Angi
"Uri Dimant" <urid@.iscar.co.il> glsD:%236RU9B1xGHA.480@.TK2MSFTNGP06.phx.gbl...[vbc
ol=seagreen]
> angi
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565984cd-60c6-4df7-83e
a-2349b838ccb2.htm
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:OgEkdW0xGHA.2400@.TK2MSFTNGP03.phx.gbl...
>[/vbcol]
I'm use SQL Server 2005 and want to know is any way can retrieve windows
user account?
I want to retrieve windows user account and insert into the table.
I don't want to use any program lanaguage just want to know SQL 2005 has any
kind of function
or store procedure can support this.
Please give me function or store procedure name and reference.
Thanks for any advice!
Angiangi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565984cd-60c6-4df7-83ea-
2349b838ccb2.htm
"angi" <angi@.news.microsoft.com> wrote in message
news:OgEkdW0xGHA.2400@.TK2MSFTNGP03.phx.gbl...
> Hi, All
> I'm use SQL Server 2005 and want to know is any way can retrieve windows
> user account?
> I want to retrieve windows user account and insert into the table.
> I don't want to use any program lanaguage just want to know SQL 2005 has
> any kind of function
> or store procedure can support this.
> Please give me function or store procedure name and reference.
> Thanks for any advice!
> Angi
>|||Thanks for Uri.
But it my fault that didn't describe it clearly.
SYSTEM_USER retrive the windows user account who login the SQL Server.
But what I want is not who login to SQL Server and retrive it.
I want is retrive all of windows user account or user name on local server,
on windows server.
Example:
There are 10 windows user accounts on Local Server, such as John, Mary, May
and so on.
They are window server's user and maybe just John has the right to access
SQL Server.
And I want to use some kinds of SP or Function to get John, Mary, May's user
name
(not just login SQL Server's user, not SYSTEM_USER just show
"LOCALHOST\John"),
or some function like LDAP can get Windows user account or user name through
the T-SQL to retrive all of the windows user account or user name.
Such AS:
SELECT xxFn ... AS Username
EXEC xxSp ... AS Username
Username
----
John
Mary
May <-- user's account on windows server (not who login sql server)
...
uh... hope someone know what is my requirement!
Thanks for help!
Angi
"Uri Dimant" <urid@.iscar.co.il> glsD:%236RU9B1xGHA.480@.TK2MSFTNGP06.phx.gbl...[vbc
ol=seagreen]
> angi
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565984cd-60c6-4df7-83e
a-2349b838ccb2.htm
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:OgEkdW0xGHA.2400@.TK2MSFTNGP03.phx.gbl...
>[/vbcol]
Saturday, February 25, 2012
Retrieve Image from SqlServer
hi all
i hav a database with some images...images r stored in binary data form
i want to retrieve that image from database and display it in a details View
im inserting images to database like this
public
void OnUpload(Object sender,EventArgs e){
int len = Upload.PostedFile.ContentLength;byte[] pic =newbyte[len];Upload.PostedFile.InputStream.Read (pic, 0, len);
SqlConnection connection =newSqlConnection ("integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbAsoftWeb;Data Source=ASP");try{
connection.Open ();
SqlCommand cmd =newSqlCommand("insert into tblStock " +"(Image, Image_Data,[DESC],PRICE,BAL_QTY,PV) values (@.pic, @.text, @.lblProd, @.lblPrice, @.lblPV, @.lblQty) ", connection);cmd.Parameters.Add(
"@.pic", pic);cmd.Parameters.Add(
"@.text", Comment.Text);cmd.Parameters.Add(
"@.lblProd", txtProdName.Text);cmd.Parameters.Add(
"@.lblPrice", txtPrice.Text);cmd.Parameters.Add(
"@.lblPV", txtPV.Text);cmd.Parameters.Add(
"@.lblQty", txtQty.Text);cmd.ExecuteNonQuery ();
}
finally{
connection.Close ();
}
im able to get the image in to form but i want to display it in a detailsview
how cani do it..can any one explain me
thanks in advance
Hari
Hi
Check those articles:
Image_In_DetailsView
Image_in_Details_View_part_2
Hope it helps.
Subscribe to:
Posts (Atom)