<ROOT> xml sql
-
Hi I am working in sql 2000 database and I want to retrieve a select statement and generate an xml. Everything goes perfect except the fact that I can't add a general root for the entire result from dhe generated xml. My code is like this:
SELECT FirstName , LastName
FROM Employees
FOR XML AUTO, ELEMENTSand the result for this is
<Employees>
<FirstName>Nancy</FirstName>
<LastName>Davolio</LastName>
</Employees>
<Employees>
<FirstName>Andrew</FirstName>
<LastName>Fuller</LastName>
</Employees>
<Employees>
<FirstName>Janet</FirstName>
<LastName>Leverling</LastName>
</Employees>
<Employees>
<FirstName>Margaret</FirstName>
<LastName>Peacock</LastName>
</Employees>but what I want is that this xml to look like this
<Table1>
<Employees>
<FirstName>Nancy</FirstName>
<LastName>Davolio</LastName>
</Employees>
<Employees>
<FirstName>Andrew</FirstName>
<LastName>Fuller</LastName>
</Employees>
<Employees>
<FirstName>Janet</FirstName>
<LastName>Leverling</LastName>
</Employees>
<Employees>
<FirstName>Margaret</FirstName>
<LastName>Peacock</LastName>
</Employees>
</Table1>thanks for any advice. p.s. I meantioning again that im workin in sql 2000
Qendro
-
Hi I am working in sql 2000 database and I want to retrieve a select statement and generate an xml. Everything goes perfect except the fact that I can't add a general root for the entire result from dhe generated xml. My code is like this:
SELECT FirstName , LastName
FROM Employees
FOR XML AUTO, ELEMENTSand the result for this is
<Employees>
<FirstName>Nancy</FirstName>
<LastName>Davolio</LastName>
</Employees>
<Employees>
<FirstName>Andrew</FirstName>
<LastName>Fuller</LastName>
</Employees>
<Employees>
<FirstName>Janet</FirstName>
<LastName>Leverling</LastName>
</Employees>
<Employees>
<FirstName>Margaret</FirstName>
<LastName>Peacock</LastName>
</Employees>but what I want is that this xml to look like this
<Table1>
<Employees>
<FirstName>Nancy</FirstName>
<LastName>Davolio</LastName>
</Employees>
<Employees>
<FirstName>Andrew</FirstName>
<LastName>Fuller</LastName>
</Employees>
<Employees>
<FirstName>Janet</FirstName>
<LastName>Leverling</LastName>
</Employees>
<Employees>
<FirstName>Margaret</FirstName>
<LastName>Peacock</LastName>
</Employees>
</Table1>thanks for any advice. p.s. I meantioning again that im workin in sql 2000
Qendro
I use neither 2000 or xml but have you tried the following...
SELECT FirstName , LastName
FROM Table1.Employees
FOR XML AUTO, ELEMENTSit seems logical, I have no idea if it will work!
Never underestimate the power of human stupidity RAH
-
Hi I am working in sql 2000 database and I want to retrieve a select statement and generate an xml. Everything goes perfect except the fact that I can't add a general root for the entire result from dhe generated xml. My code is like this:
SELECT FirstName , LastName
FROM Employees
FOR XML AUTO, ELEMENTSand the result for this is
<Employees>
<FirstName>Nancy</FirstName>
<LastName>Davolio</LastName>
</Employees>
<Employees>
<FirstName>Andrew</FirstName>
<LastName>Fuller</LastName>
</Employees>
<Employees>
<FirstName>Janet</FirstName>
<LastName>Leverling</LastName>
</Employees>
<Employees>
<FirstName>Margaret</FirstName>
<LastName>Peacock</LastName>
</Employees>but what I want is that this xml to look like this
<Table1>
<Employees>
<FirstName>Nancy</FirstName>
<LastName>Davolio</LastName>
</Employees>
<Employees>
<FirstName>Andrew</FirstName>
<LastName>Fuller</LastName>
</Employees>
<Employees>
<FirstName>Janet</FirstName>
<LastName>Leverling</LastName>
</Employees>
<Employees>
<FirstName>Margaret</FirstName>
<LastName>Peacock</LastName>
</Employees>
</Table1>thanks for any advice. p.s. I meantioning again that im workin in sql 2000
Qendro
SELECT FirstName , LastName
FROM Employees
FOR XML AUTO, ELEMENTS, ROOT('Table1')http://msdn.microsoft.com/en-us/library/ms190922.aspx[^]
I know the language. I've read a book. - _Madmatt
-
I use neither 2000 or xml but have you tried the following...
SELECT FirstName , LastName
FROM Table1.Employees
FOR XML AUTO, ELEMENTSit seems logical, I have no idea if it will work!
Never underestimate the power of human stupidity RAH
Isn't that what the OP had :confused:
I know the language. I've read a book. - _Madmatt
-
Isn't that what the OP had :confused:
I know the language. I've read a book. - _Madmatt
Not quite, I added Table1. to employees. I was hoping the dot notation would work. I note you gave him the correct answer so I learn something yet again!
Never underestimate the power of human stupidity RAH
-
I use neither 2000 or xml but have you tried the following...
SELECT FirstName , LastName
FROM Table1.Employees
FOR XML AUTO, ELEMENTSit seems logical, I have no idea if it will work!
Never underestimate the power of human stupidity RAH
-
It is totally illogical unless the Table employees exists in the Table1 schema; and why would you even have a schema called table1?
Take a look at the XML supplied by the OP, note the hierarchy of nodes, see where he added Table1 as the PARENT node to the employee records, it it not logical that the parent node may prefix the employee node when defining the location in the xml data.
SilimSayo wrote:
unless the Table employees exists in the Table1 schema
Besides what has schema got to do with XML data?
SilimSayo wrote:
and why would you even have a schema called table1?
Schema, what schema, who mentioned schema. If you think a schema called table1 is dumb, hang around here for a while, you see some doozies in the forums.
Never underestimate the power of human stupidity RAH
-
Take a look at the XML supplied by the OP, note the hierarchy of nodes, see where he added Table1 as the PARENT node to the employee records, it it not logical that the parent node may prefix the employee node when defining the location in the xml data.
SilimSayo wrote:
unless the Table employees exists in the Table1 schema
Besides what has schema got to do with XML data?
SilimSayo wrote:
and why would you even have a schema called table1?
Schema, what schema, who mentioned schema. If you think a schema called table1 is dumb, hang around here for a while, you see some doozies in the forums.
Never underestimate the power of human stupidity RAH
If you were to use a fully qualified name when selecting from table employee, you would write Select * from mydatabase.myschema.employee. If you use a partially qualified name you would write: Select * from myschema.employee or Select * from mydatabase..employee So Table1.Employee is interpreted as a table called Employee in a schema called Table1.
-
If you were to use a fully qualified name when selecting from table employee, you would write Select * from mydatabase.myschema.employee. If you use a partially qualified name you would write: Select * from myschema.employee or Select * from mydatabase..employee So Table1.Employee is interpreted as a table called Employee in a schema called Table1.
:sigh: The OP is using XML. I know what a schema is I have been working with databases for 20+ years. What I have not worked with is XML and they are NOT the same thing. You're discussion about schema is totally irrelevant to the OPs question.
Never underestimate the power of human stupidity RAH
-
:sigh: The OP is using XML. I know what a schema is I have been working with databases for 20+ years. What I have not worked with is XML and they are NOT the same thing. You're discussion about schema is totally irrelevant to the OPs question.
Never underestimate the power of human stupidity RAH
I understand what you are saying but you're not getting my point at all. I am saying that the "compiler" would think that Table1 is a schema because it prefixes the table name employee. In other words, don't write Table1.employee because Table1 would understood differently by the "compiler". If you're worked for 20 years plus with a database, this should be pretty much common sense. I don't know much about how to generate XML but if do a little googling, you should be able to find the answer especially for someone with 20+ years in the IT industry.
-
I understand what you are saying but you're not getting my point at all. I am saying that the "compiler" would think that Table1 is a schema because it prefixes the table name employee. In other words, don't write Table1.employee because Table1 would understood differently by the "compiler". If you're worked for 20 years plus with a database, this should be pretty much common sense. I don't know much about how to generate XML but if do a little googling, you should be able to find the answer especially for someone with 20+ years in the IT industry.
SilimSayo wrote:
but you're not getting my point at all
You are quite correct in that, I was only focusing on the question of the OP, not on a general discussion of how the SQL was going to interpret the statement.
Never underestimate the power of human stupidity RAH