Gerating a xml file from oracle 9i in a asp program

Hi, is any of the user of that list works with Oracle9i ? because i need to generate xml file from a intranet asp site ... so i don't know where to find information ...
In fact, i want to make a xml file from a sql reqest from oracle in a asp program ...is someone did that ? how i can read a recorset if i use sqlxml of oracle ?
thanks in advance
cheers
jp
With SQL Server you could use the FOR XML clause to get the data out in XML
format. If you need help for the same, with Oracle, then please post this to
an Oracle newsgroup.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:20812732-5D8B-4374-8383-763B56F688AD@.microsoft.com...
Hi, is any of the user of that list works with Oracle9i ? because i need to
generate xml file from a intranet asp site ... so i don't know where to find
information ...
In fact, i want to make a xml file from a sql reqest from oracle in a asp
program ...is someone did that ? how i can read a recorset if i use sqlxml
of oracle ?
thanks in advance
cheers
jp

Generating XMLdata based on the schema

How to generate XMLdata based on the schema in SQL SERVER 2005?

Are there any features in SQLServer2005 to achieve this using Xml datatype ,SQLXML 4.0?

Please suggest me in this regard.

Thanks

vaishu

SQL Server has the FOR XML xml construction modes which you can use to generate XML from your relational schema. Examples of this support are here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

In addition SQLXML 4.0 has mappings from XSD schema to relational schema, in case you need to map between these two types for schemas. An introduction to these can be found here:

http://msdn2.microsoft.com/en-us/library/ms171870(SQL.90).aspx

|||

Thanks for the reply

you can close treat this request as closed.

vaishu

Generating XMLdata based on the schema

How to generate XMLdata based on the schema in SQL SERVER 2005?

Are there any features in SQLServer2005 to achieve this using Xml datatype ,SQLXML 4.0?

Please suggest me in this regard.

Thanks

vaishu

SQL Server has the FOR XML xml construction modes which you can use to generate XML from your relational schema. Examples of this support are here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

In addition SQLXML 4.0 has mappings from XSD schema to relational schema, in case you need to map between these two types for schemas. An introduction to these can be found here:

http://msdn2.microsoft.com/en-us/library/ms171870(SQL.90).aspx

|||

Thanks for the reply

you can close treat this request as closed.

vaishu

generating XML to validated against the schema

hi,
I have a sample xml data and sample xsd as follows: the xsd is really big
and runs into 4 pages. I need to stored data into the data base
and generate xml that validates against this schema.
the sqlxml bulk load and view mapper fails to recognize the xsd fine.
Can some one point me to the right direction .
Infact storing is working but generating the xml
using EXPLICIT option is really a pain.
Regards,
Balajee
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="StrategicPerformanceContract"
targetNamespace="http://tempuri.org/StrategicPerformanceContract.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/StrategicPerformanceContract.xsd"
xmlns:mstns="http://tempuri.org/StrategicPerformanceContract.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:annotation>
<xs:documentation>Strategic Performance Contract</xs:documentation>
</xs:annotation>
<xs:element name="root" type="contractType" />
<xs:complexType name="contractType">
<xs:sequence>
<xs:element name="transactionHeader" type="transactionHeaderType" />
<xs:element name="documentHeader" type="documentHeaderType" />
<xs:element name="strategicDirection" type="strategicDirectionType" />
<xs:element name="strategicChoices" type="strategicChoicesType" />
<xs:element name="riskManagement" type="riskManagementType" />
<xs:element name="performanceContract" type="performanceContractType" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="transactionHeaderType">
<xs:sequence>
<xs:element name="userName" type="xs:string" />
</xs:sequence>
<xs:attribute name="gid" type="xs:unsignedLong"/>
</xs:complexType>
<xs:complexType name="documentHeaderType">
<xs:sequence>
<xs:element name="contractType" type="xs:ID" />
<xs:element name="abbr" type="xs:string" />
<xs:element name="leftTitle1" type="xs:string" />
<xs:element name="leftTitle2" type="xs:string" />
<xs:element name="rightTitle1" type="xs:string" />
<xs:element name="asofDate" type="xs:string" />
</xs:sequence>
<xs:attribute name="gid" type="xs:unsignedLong"/>
</xs:complexType>
<xs:complexType name="strategicDirectionType">
<xs:sequence>
<xs:element name="header" type="headerType" />
<xs:element name="strategicObjective" type="strategicObjectiveType"
minOccurs="0" maxOccurs="unbounded" />
<xs:element name="footnotes" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="headerType">
<xs:sequence>
<xs:element name="vpuMission" type="xs:string" />
<xs:element name="strategicContext" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="strategicObjectiveType">
<xs:sequence>
<xs:element name="objective" type="objectiveType" />
<xs:element name="subObjective" type="objectiveType" minOccurs="0"
maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
What are the problems you get when using the Schema and adding mapping
annotations?
Thanks
Michael
"balajee" <balajee@.discussions.microsoft.com> wrote in message
news:65C72C0A-D8C6-4CA3-ADD4-CBACEB16F58F@.microsoft.com...
> hi,
> I have a sample xml data and sample xsd as follows: the xsd is really big
> and runs into 4 pages. I need to stored data into the data base
> and generate xml that validates against this schema.
> the sqlxml bulk load and view mapper fails to recognize the xsd fine.
> Can some one point me to the right direction .
> Infact storing is working but generating the xml
> using EXPLICIT option is really a pain.
> Regards,
> Balajee
>
> <?xml version="1.0" encoding="utf-8" ?>
> <xs:schema id="StrategicPerformanceContract"
> targetNamespace="http://tempuri.org/StrategicPerformanceContract.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/StrategicPerformanceContract.xsd"
> xmlns:mstns="http://tempuri.org/StrategicPerformanceContract.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:annotation>
> <xs:documentation>Strategic Performance Contract</xs:documentation>
> </xs:annotation>
> <xs:element name="root" type="contractType" />
> <xs:complexType name="contractType">
> <xs:sequence>
> <xs:element name="transactionHeader" type="transactionHeaderType" />
> <xs:element name="documentHeader" type="documentHeaderType" />
> <xs:element name="strategicDirection" type="strategicDirectionType" />
> <xs:element name="strategicChoices" type="strategicChoicesType" />
> <xs:element name="riskManagement" type="riskManagementType" />
> <xs:element name="performanceContract" type="performanceContractType" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="transactionHeaderType">
> <xs:sequence>
> <xs:element name="userName" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="gid" type="xs:unsignedLong"/>
> </xs:complexType>
> <xs:complexType name="documentHeaderType">
> <xs:sequence>
> <xs:element name="contractType" type="xs:ID" />
> <xs:element name="abbr" type="xs:string" />
> <xs:element name="leftTitle1" type="xs:string" />
> <xs:element name="leftTitle2" type="xs:string" />
> <xs:element name="rightTitle1" type="xs:string" />
> <xs:element name="asofDate" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="gid" type="xs:unsignedLong"/>
> </xs:complexType>
> <xs:complexType name="strategicDirectionType">
> <xs:sequence>
> <xs:element name="header" type="headerType" />
> <xs:element name="strategicObjective" type="strategicObjectiveType"
> minOccurs="0" maxOccurs="unbounded" />
> <xs:element name="footnotes" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="headerType">
> <xs:sequence>
> <xs:element name="vpuMission" type="xs:string" />
> <xs:element name="strategicContext" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="strategicObjectiveType">
> <xs:sequence>
> <xs:element name="objective" type="objectiveType" />
> <xs:element name="subObjective" type="objectiveType" minOccurs="0"
> maxOccurs="unbounded" />
> </xs:sequence>
> </xs:complexType>
|||Michael,
I am trying to map the schema using sql viewmapper and
it gives me an error :
Error while parsing
:Incorrect definition for the root element in schema.
Line 7 Position 63
xmlns:xs="http://www.w3.org/2001/XMLSchema">
regards,
Balajee
"Michael Rys [MSFT]" wrote:

> What are the problems you get when using the Schema and adding mapping
> annotations?
> Thanks
> Michael
> "balajee" <balajee@.discussions.microsoft.com> wrote in message
> news:65C72C0A-D8C6-4CA3-ADD4-CBACEB16F58F@.microsoft.com...
>
>
|||Unfortunately, the SQL Viewmapper only works for the older Microsoft schema
language known as XDR and not with the W3C XML Schema language (that you
seem to be using). This unfortunately means that you either need a
third-party tool vendor or notepad.
Best regards
Michael
"balajee" <balajee@.discussions.microsoft.com> wrote in message
news:0EA7E4AD-F94D-4021-8A22-FE327F1B786B@.microsoft.com...[vbcol=seagreen]
> Michael,
> I am trying to map the schema using sql viewmapper and
> it gives me an error :
> Error while parsing
> :Incorrect definition for the root element in schema.
> Line 7 Position 63
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
>
> regards,
> Balajee
>
> "Michael Rys [MSFT]" wrote:
sql

Generating XML Schema from db tables - including field lengths

I'm working on a C# project that essentially features a dump of the
database (on SQL Server 2005) into an XML file, with schema.
Basically we want the users to be able to take this data anywhere, as
easily as is possible. I initially selected the information "For XML
Auto" etc. etc. but found that when I tested simple imports of that
XML with the likes of Access and Excel, those programs didn't take
well to the schema at all.
Switching gears, I queried the databases normally and did the XML
conversion in C# using the dataset.writeXML function with schema,
which generated schema that made Access and Excel much happier. The
only thing is that Access, upon creating tables based on the schema,
took the string fields to be text, rather than memo. (i.e.
varchar(255) instead of text) ... likewise, SQL Server, when
wrestling to create tables based on the schema, behaves similarly.
Mind you, when the tables are created beforehand, and just populated
with the XML data, that's fine, it works great.
I do realize that's how it's supposed to work, but I'm being asked to
generate a schema that includes maxLength for varchar fields -- even
though I doubt any program that would be importing this data would
even be able to read that from the schema and use it appropriately.
I'm hoping someone here can tell me there's a nicer way of doing that
than SELECTing FOR XML RAW and drawing up an XSLT.
Takers?Did you use the xmlschema directive on FOR XML AUTO or RAW?
Best regards
Michael
"Matthew Dunphy" <leviathant@.gmail.com> wrote in message
news:1172246553.987853.109810@.j27g2000cwj.googlegroups.com...
> I'm working on a C# project that essentially features a dump of the
> database (on SQL Server 2005) into an XML file, with schema.
> Basically we want the users to be able to take this data anywhere, as
> easily as is possible. I initially selected the information "For XML
> Auto" etc. etc. but found that when I tested simple imports of that
> XML with the likes of Access and Excel, those programs didn't take
> well to the schema at all.
> Switching gears, I queried the databases normally and did the XML
> conversion in C# using the dataset.writeXML function with schema,
> which generated schema that made Access and Excel much happier. The
> only thing is that Access, upon creating tables based on the schema,
> took the string fields to be text, rather than memo. (i.e.
> varchar(255) instead of text) ... likewise, SQL Server, when
> wrestling to create tables based on the schema, behaves similarly.
> Mind you, when the tables are created beforehand, and just populated
> with the XML data, that's fine, it works great.
> I do realize that's how it's supposed to work, but I'm being asked to
> generate a schema that includes maxLength for varchar fields -- even
> though I doubt any program that would be importing this data would
> even be able to read that from the schema and use it appropriately.
> I'm hoping someone here can tell me there's a nicer way of doing that
> than SELECTing FOR XML RAW and drawing up an XSLT.
> Takers?
>|||Both -- when I used AUTO, it worked great except that it doesn't
output the maxlength for the varchar fields. Everything else it does
is kind of magical.
When I use RAW, it outputs each column, but again, nothing about the
(3000) for a row that is varchar(3000).
To be specific, the select statement that comes closest to what I want
looks basically like this:
SELECT *
FROM table
WHERE id=@.id
FOR XML AUTO, ELEMENTS, XMLDATA
(I do this for about a dozen tables)
In the resulting schema, the ElementType nodes have attributes for
name, content, model, and dt:type... is there some way to also specify
maxLength, based off the table schema in SQL server, so that I can do
a simple query like this -- or do I just have to bite the bullet and
write the schemas manually?
Thanks!
--Matt Dunphy
On Feb 24, 1:42 am, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> Did you use the xmlschema directive on FOR XML AUTO or RAW?
> Best regards
> Michael
> "Matthew Dunphy" <leviath...@.gmail.com> wrote in message
> news:1172246553.987853.109810@.j27g2000cwj.googlegroups.com...
>
>
>
>|||Actually... never mind that last post, I think you've pointed me in
the right direction. (That's what I get for posting first thing in
the morning!) Thanks for your help.
On Feb 26, 9:14 am, "Matthew Dunphy" <leviath...@.gmail.com> wrote:
> Both -- when I used AUTO, it worked great except that it doesn't
> output the maxlength for the varchar fields. Everything else it does
> is kind of magical.
> When I use RAW, it outputs each column, but again, nothing about the
> (3000) for a row that is varchar(3000).
> To be specific, the select statement that comes closest to what I want
> looks basically like this:
> SELECT *
> FROM table
> WHERE id=@.id
> FOR XML AUTO, ELEMENTS, XMLDATA
> (I do this for about a dozen tables)
> In the resulting schema, the ElementType nodes have attributes for
> name, content, model, and dt:type... is there some way to also specify
> maxLength, based off the table schema in SQL server, so that I can do
> a simple query like this -- or do I just have to bite the bullet and
> write the schemas manually?
> Thanks!
> --Matt Dunphy
>

Generating XML Schema from db tables - including field lengths

I'm working on a C# project that essentially features a dump of the
database (on SQL Server 2005) into an XML file, with schema.
Basically we want the users to be able to take this data anywhere, as
easily as is possible. I initially selected the information "For XML
Auto" etc. etc. but found that when I tested simple imports of that
XML with the likes of Access and Excel, those programs didn't take
well to the schema at all.
Switching gears, I queried the databases normally and did the XML
conversion in C# using the dataset.writeXML function with schema,
which generated schema that made Access and Excel much happier. The
only thing is that Access, upon creating tables based on the schema,
took the string fields to be text, rather than memo. (i.e.
varchar(255) instead of text) ... likewise, SQL Server, when
wrestling to create tables based on the schema, behaves similarly.
Mind you, when the tables are created beforehand, and just populated
with the XML data, that's fine, it works great.
I do realize that's how it's supposed to work, but I'm being asked to
generate a schema that includes maxLength for varchar fields -- even
though I doubt any program that would be importing this data would
even be able to read that from the schema and use it appropriately.
I'm hoping someone here can tell me there's a nicer way of doing that
than SELECTing FOR XML RAW and drawing up an XSLT.
Takers?
Did you use the xmlschema directive on FOR XML AUTO or RAW?
Best regards
Michael
"Matthew Dunphy" <leviathant@.gmail.com> wrote in message
news:1172246553.987853.109810@.j27g2000cwj.googlegr oups.com...
> I'm working on a C# project that essentially features a dump of the
> database (on SQL Server 2005) into an XML file, with schema.
> Basically we want the users to be able to take this data anywhere, as
> easily as is possible. I initially selected the information "For XML
> Auto" etc. etc. but found that when I tested simple imports of that
> XML with the likes of Access and Excel, those programs didn't take
> well to the schema at all.
> Switching gears, I queried the databases normally and did the XML
> conversion in C# using the dataset.writeXML function with schema,
> which generated schema that made Access and Excel much happier. The
> only thing is that Access, upon creating tables based on the schema,
> took the string fields to be text, rather than memo. (i.e.
> varchar(255) instead of text) ... likewise, SQL Server, when
> wrestling to create tables based on the schema, behaves similarly.
> Mind you, when the tables are created beforehand, and just populated
> with the XML data, that's fine, it works great.
> I do realize that's how it's supposed to work, but I'm being asked to
> generate a schema that includes maxLength for varchar fields -- even
> though I doubt any program that would be importing this data would
> even be able to read that from the schema and use it appropriately.
> I'm hoping someone here can tell me there's a nicer way of doing that
> than SELECTing FOR XML RAW and drawing up an XSLT.
> Takers?
>
|||Both -- when I used AUTO, it worked great except that it doesn't
output the maxlength for the varchar fields. Everything else it does
is kind of magical.
When I use RAW, it outputs each column, but again, nothing about the
(3000) for a row that is varchar(3000).
To be specific, the select statement that comes closest to what I want
looks basically like this:
SELECT *
FROM table
WHERE id=@.id
FOR XML AUTO, ELEMENTS, XMLDATA
(I do this for about a dozen tables)
In the resulting schema, the ElementType nodes have attributes for
name, content, model, and dt:type... is there some way to also specify
maxLength, based off the table schema in SQL server, so that I can do
a simple query like this -- or do I just have to bite the bullet and
write the schemas manually?
Thanks!
--Matt Dunphy
On Feb 24, 1:42 am, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> Did you use the xmlschema directive on FOR XML AUTO or RAW?
> Best regards
> Michael
> "Matthew Dunphy" <leviath...@.gmail.com> wrote in message
> news:1172246553.987853.109810@.j27g2000cwj.googlegr oups.com...
>
>
|||Actually... never mind that last post, I think you've pointed me in
the right direction. (That's what I get for posting first thing in
the morning!) Thanks for your help.
On Feb 26, 9:14 am, "Matthew Dunphy" <leviath...@.gmail.com> wrote:
> Both -- when I used AUTO, it worked great except that it doesn't
> output the maxlength for the varchar fields. Everything else it does
> is kind of magical.
> When I use RAW, it outputs each column, but again, nothing about the
> (3000) for a row that is varchar(3000).
> To be specific, the select statement that comes closest to what I want
> looks basically like this:
> SELECT *
> FROM table
> WHERE id=@.id
> FOR XML AUTO, ELEMENTS, XMLDATA
> (I do this for about a dozen tables)
> In the resulting schema, the ElementType nodes have attributes for
> name, content, model, and dt:type... is there some way to also specify
> maxLength, based off the table schema in SQL server, so that I can do
> a simple query like this -- or do I just have to bite the bullet and
> write the schemas manually?
> Thanks!
> --Matt Dunphy
>

Generating XML Schema for SQL Server Table

how can persist schema for a sql server table into an xml file from .net application?

thanks

If you already loaded the structure in a dataset, you can just use the WriteXMLSchema Method on the dataset object.

http://msdn2.microsoft.com/en-us/library/system.data.dataset.writexmlschema.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de

generating xml from sql server

Hi,
I have generated classes form an XSD (where complex), currently I am
reading the database and filling he classes and serializinf the classes to
get the xml. The problem is there are a lot of records (in the range from
10000 +) and looping the dataset to fill will take a long time. Is there any
other approach to do this.
Can i use SQLXML to generate the xml is this a good idea?
Any thought are welcome.
ThanksUsing the SQLXMLBulLoad utility seems like a good solution for your
scenario. See BOL or MSDN for more information on this topic. I see a lot of
newsgroup postings on SQLXMLBulLoad - that can also give you some idea.
Thank you,
Shankar
This posting is provided "AS IS" with no warranties, and confers no rights.
"Error while executing SP" <ErrorwhileexecutingSP@.discussions.microsoft.com>
wrote in message news:092A4CCB-F3D0-4860-8DF5-666B048A925B@.microsoft.com...
> Hi,
> I have generated classes form an XSD (where complex), currently I am
> reading the database and filling he classes and serializinf the classes to
> get the xml. The problem is there are a lot of records (in the range from
> 10000 +) and looping the dataset to fill will take a long time. Is there
> any
> other approach to do this.
> Can i use SQLXML to generate the xml is this a good idea?
> Any thought are welcome.
> Thanks
>

generating xml from sql server

Hi,
I have generated classes form an XSD (where complex), currently I am
reading the database and filling he classes and serializinf the classes to
get the xml. The problem is there are a lot of records (in the range from
10000 +) and looping the dataset to fill will take a long time. Is there any
other approach to do this.
Can i use SQLXML to generate the xml is this a good idea?
Any thought are welcome.
Thanks
Using the SQLXMLBulLoad utility seems like a good solution for your
scenario. See BOL or MSDN for more information on this topic. I see a lot of
newsgroup postings on SQLXMLBulLoad - that can also give you some idea.
Thank you,
Shankar
This posting is provided "AS IS" with no warranties, and confers no rights.
"Error while executing SP" <ErrorwhileexecutingSP@.discussions.microsoft.com >
wrote in message news:092A4CCB-F3D0-4860-8DF5-666B048A925B@.microsoft.com...
> Hi,
> I have generated classes form an XSD (where complex), currently I am
> reading the database and filling he classes and serializinf the classes to
> get the xml. The problem is there are a lot of records (in the range from
> 10000 +) and looping the dataset to fill will take a long time. Is there
> any
> other approach to do this.
> Can i use SQLXML to generate the xml is this a good idea?
> Any thought are welcome.
> Thanks
>
sql

generating XML from SQL

Hi all,

Can anybody advice me how can i go about creating an XML from SQL. The need arised when our jobs which create Xmls from the select statement using vbscript hang in the executing state because the file gets a sharing voilation so we have to change the generated filename on the vbscript code frquently so i thought about can i generate it directly from SQL......

Any ideas ?

this solution would be very helpful for me

Thanks in ADVANCE

Jacx

This may help you

XML support in SQL Server 2005 (just in case you're not familiar with what is available):
http://msdn2.microsoft.com/en-us/library/ms345117.aspx#sql2k5xml_topic3

Constructing XML using SQL:
http://msdn2.microsoft.com/en-us/ms178107.aspx

Generating XML data based on the Schema

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Can a stored procedure in SQL Server 2005 generate XML data based on the schema?

Yes...

Create Proc dbo.GenXMLWithSchemaValidation
As
Begin
Set Nocount On

DECLARE @.x XML (HumanResources.HRResumeSchemaCollection)
SET @.x = (
SELECT ContactID,
FirstName,
LastName,
AdditionalContactInfo.query('
declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number') as MorePhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE)
SELECT @.x
Set Nocount Off
End

Hope this helps,

Derek

|||

Thanks Derek

i appreciate your help.

Vaishu

Generating XML data based on the Schema

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Can a stored procedure in SQL Server 2005 generate XML data based on the schema?

Yes...

Create Proc dbo.GenXMLWithSchemaValidation
As
Begin
Set Nocount On

DECLARE @.x XML (HumanResources.HRResumeSchemaCollection)
SET @.x = (
SELECT ContactID,
FirstName,
LastName,
AdditionalContactInfo.query('
declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number') as MorePhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE)
SELECT @.x
Set Nocount Off
End

Hope this helps,

Derek

|||

Thanks Derek

i appreciate your help.

Vaishu

Generating XML based on XMLSchema in a SQLSERVER2005 Stored procedure

Can a stored procedure in SQLSERVER 2005 generate XMLdata based on the schema?

Are there any features in SQLServer2005 to achieve this ?

Please suggest me in this regard.

Thanks

vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

|||

thanks for the reply.

vaishu

|||

Can sqlserver 2005 generete XML schema for the whole database ?

Can anyone suggest us on this.

thanks

oak-net

Generating XML based on XMLSchema in a SQLSERVER2005 Stored procedure

Can a stored procedure in SQLSERVER 2005 generate XMLdata based on the schema?

Are there any features in SQLServer2005 to achieve this ?

Please suggest me in this regard.

Thanks

vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

|||

thanks for the reply.

vaishu

|||

Can sqlserver 2005 generete XML schema for the whole database ?

Can anyone suggest us on this.

thanks

oak-net

sql

Generating XML based on Schema

Hi,

i need Info regarding this

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

Can i find any article on how to do this.

Thanks,

Vaishu

How about using "FOR XML AUTO" in your select!

select * from tbl1 FOR XML AUTO

generating script with SMO

I am taking my table names and colums from xml file and then generating the script and executing it.I have problem creating the script for incerting table with multiple colums.Maybe the problem is that I am using a for loop to get all column names from a array string.Has anyone idea why it is not working? When I try insert of a table without loop it is wirking!Here is the code :
Server server = new Server(); // Create table in my personal database Database db = server.Databases["new"]; // Make Sure Constraint is Scripted ScriptingOptions so = new ScriptingOptions(); so.IncludeHeaders = true; so.SchemaQualify = true; // Create In-Memory Table Table newTable = new Table(db, Names[0].ToString()); for (int i = 1; i < Names.Length; i++) { Column newColumn = new Column(newTable,Names); newTable.Columns.Add(newColumn); } foreach (string s in newTable.Script(so)) /// Error generating the script { myTableScript = myTableScript + s; } db.ExecuteNonQuery(myTableScript); //disconnect form Database connection.Close();

Could you please metion the full error you are getting.

Thanks,
Kuntal

|||I fixed the problem.I haven't set the column format and that's why the script was not correctly generated.The loop is functioning now ok. Here is the edit line :Column newColumn = new Column(newTable, attr.Name, DataType.NVarChar(50));Now I can easily take the table and column names from the xml file and directly put them without using array strings.Thanks for yor time :) Have a nice day !

Generating Multi Level nodes in Stored Procedures

Hi all,

What I am trying to do is generate a stored procedure that is desired
to output XML in this type of format

<Parent Device>
<Device>
<Device ID>1</DeviceID>
<ChildRegister>
<ChildRegisterID>22</ChildRegisterID>
</ChildRegister>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegister>
<ChildRegisterID>23</ChildRegisterID>
</ChildRegister>
</Device>
</Parent Device
The area of concern is the child register, the XML being generated
disregards the Device the ChildRegister belongs to and always places it
as elements of the last device.

<Parent Device>
<Device>
<Device ID>1</DeviceID>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegister>
<ChildRegisterID>23</ChildRegisterID>
</ChildRegister>
<ChildRegister>
<ChildRegisterID>22</ChildRegisterID>
</ChildRegister>
</Device>
</Parent Device
I am trying to produce XML like the first one I described and have yet
to discover a way of associating the ChildRegister with the parent
Device in XML. I am not sure if it is a limitation of SQL Server, or if
my implementation is incorrect. If anyone could post hints or
solutions, I would greatly appreciate it.
A shortened version of the stored procedure is below

Cheers :)
Alvin

SELECT
1AS TAG
,NULL AS PARENT
,NULL AS [Device!2!DeviceID!element]
,NULL AS [ChildRegister!3!RegisterID!element]

FROM udetails INNER JOIN
Detail ON udetails.ID = Detail.ID
WHERE (uDetails.JobID = @.ID)

UNION ALL

SELECT
2 AS TAG
,1 AS PARENT
,TempTable.DeviceIDAS [Device!2!DeviceID!element]
,NULL AS [ChildRegister!3!RegisterID!element]

>From #Temp as TempTable INNER JOIN
device ON TempTable.DeviceID = device.DeviceID

UNION ALL

SELECT
3 AS TAG
,2 AS PARENT
,NULL AS [Device!2!DeviceID!element]
,RegisterID AS [ChildRegister!3!RegisterID!element]

FROM #Temp t INNER JOIN
register ON t.DeviceID =
register.DeviceID

FOR XML EXPLICIT(teohster@.gmail.com) writes:
> I am trying to produce XML like the first one I described and have yet
> to discover a way of associating the ChildRegister with the parent
> Device in XML. I am not sure if it is a limitation of SQL Server, or if
> my implementation is incorrect. If anyone could post hints or
> solutions, I would greatly appreciate it.
> A shortened version of the stored procedure is below

For all problems like this, it is a good idea to post:

o CREATE TABLE statements of the tables inolved.
o INSERT statements with sample data.
o The desired output given the sample data.

You posted the last, but not the first two.

This permits people to post a tested solution to your query. In this
case, an aggrevating factor is that I am not extremely versed in XML,
so I would have to play around with the query.

It may be more effective to ask the real pros in
microsoft.public.sqlserver.xml though.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Generating a value list using PATH mode

I am attempting to get data using the "for xml path" and would like to list my data in child elements. I can render the data in the format that I would want but instead of creating child elements I am left with HTML like character codes around my element name (&lt and &gt).

I am following the example outlined in http://msdn2.microsoft.com/en-us/library/ms189885.aspx section C. The example unfortunately even shows the &lt and &gt symbols. I am missing something obvious here? Any help would be greatly appreciated.

Are you using a nested query? If so add the TYPE directive to the FOR XML e.g.

Code Snippet

SELECT ProductModelID as "@.ProductModelID",

Name as "@.ProductModelName",

(SELECT ProductID as "data()"

FROM Production.Product

WHERE Production.Product.ProductModelID =

Production.ProductModel.ProductModelID

FOR XML PATH ('')

) as "@.ProductIDs",

(

SELECT Name as "ProductName"

FROM Production.Product

WHERE Production.Product.ProductModelID =

Production.ProductModel.ProductModelID

FOR XML PATH (''), TYPE

) as "ProductNames"

FROM Production.ProductModel

WHERE ProductModelID= 7 or ProductModelID=9

FOR XML PATH('ProductModelData')

that way the XML is not escaped.

Generating a namespace prefix with For XML Explicit

Hello,
How do I get my FOR XML explicit to qualify the returned elements with a
namespace prefix?
I want:
<s1:NewOrderLine xmlns:s1 ="www.company.com">
<s1:Quantity>23</s1:Quantity>
</s1:NewOrderLine>
not:
<NewOrderLine>
<Quantity>23</Quantity>
<NewOrderLine>
Thanks -
Simon
In SQL Server 2000, you need to do it explicitly add them.
Something like (if you are using auto mode):
select a as "s1:a", 'uri:example' as "xmlns:s1"
from t as "s1:t"
for xml auto
if you want to make element-centric mappings, you will need to use the
EXPLICIT mode.
Note in SQL Server 2005, there will be an easier way to specify XML
namespaces using the WITH XMLNAMESPACES clause.
Best regards
Michael
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:418B0654-6075-4FB6-8C2C-845B65E1E221@.microsoft.com...
> Hello,
> How do I get my FOR XML explicit to qualify the returned elements with a
> namespace prefix?
> I want:
> <s1:NewOrderLine xmlns:s1 ="www.company.com">
> <s1:Quantity>23</s1:Quantity>
> </s1:NewOrderLine>
> not:
> <NewOrderLine>
> <Quantity>23</Quantity>
> <NewOrderLine>
> Thanks -
> Simon
>
|||Thanks for the reply, Michael. I wonder if you can elaborate on the EXPLICIT
mode option - with the query below I can get these results, but as you can
see, what I don't have is the 'xmlns:s1 ="www.company.com" in the root
element. Is it possible to change the query below to get that, or,
alternately, is it possible to load this result into an XMLDocument and then
add the namespace declaration?
<ns:SimpleTest>
<ns:Customers>
<ns:CompanyName>Alfreds Futterkiste</ns:CompanyName>
</ns:Customers>
</ns:SimpleTest>
from query:
select 1 as Tag,
Null as Parent,
Null as [ns:Sample!1!!element],
Null as [ns:Customers!2!ns:CompanyName!element],
Null as [ns:Customers!2!ns:CustomerID!hide]
UNION ALL
select 2 as Tag,
1 as Parent ,
Null as [ns:Sample!1!!element],
[Customers1].[CompanyName] as [ns:Customers!2!ns:CompanyName!element],
[Customers1].[CustomerID] as [ns:Customers!2!ns:CustomerID!hide]
FROM
(select
[Customers].[CompanyName] as [CompanyName],
[Customers].[CustomerID] as [CustomerID]
from [Customers])
as [Customers1]
Order By [ns:Sample!1!!element] , [ns:Customers!2!ns:CustomerID!hide] , Tag
FOR XML EXPLICIT
"Michael Rys [MSFT]" wrote:

> In SQL Server 2000, you need to do it explicitly add them.
> Something like (if you are using auto mode):
> select a as "s1:a", 'uri:example' as "xmlns:s1"
> from t as "s1:t"
> for xml auto
> if you want to make element-centric mappings, you will need to use the
> EXPLICIT mode.
> Note in SQL Server 2005, there will be an easier way to specify XML
> namespaces using the WITH XMLNAMESPACES clause.
> Best regards
> Michael
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:418B0654-6075-4FB6-8C2C-845B65E1E221@.microsoft.com...
>
>
|||Simon,
I think the trick you are looking for is to treat xmlns as an
attribute:
Here is an example based on your request:
select 1 as tag, null as parent,
'www.company.com' as [s1:NewOrderLine!1!xmlns:s1],
null as [s1:Quantity!2]
UNION
select 2 as tag, 1 as parent,
'www.company.com' as [s1:NewOrderLine!1!xmlns:s1],
23 as [s1:Quantity!2]
ORDER BY TAG,PARENT
FOR XML EXPLICIT
This would produce the following:
<s1:NewOrderLine xmlns:s1="www.company.com">
<s1:Quantity>23</s1:Quantity>
</s1:NewOrderLine>
HTH,
Todd
Simon wrote:
> Thanks for the reply, Michael. I wonder if you can elaborate on the
EXPLICIT
> mode option - with the query below I can get these results, but as
you can
> see, what I don't have is the 'xmlns:s1 ="www.company.com" in the
root
> element. Is it possible to change the query below to get that, or,
> alternately, is it possible to load this result into an XMLDocument
and then
> add the namespace declaration?
> <ns:SimpleTest>
> <ns:Customers>
> <ns:CompanyName>Alfreds Futterkiste</ns:CompanyName>
> </ns:Customers>
> </ns:SimpleTest>
> from query:
> select 1 as Tag,
> Null as Parent,
> Null as [ns:Sample!1!!element],
> Null as [ns:Customers!2!ns:CompanyName!element],
> Null as [ns:Customers!2!ns:CustomerID!hide]
> UNION ALL
> select 2 as Tag,
> 1 as Parent ,
> Null as [ns:Sample!1!!element],
> [Customers1].[CompanyName] as
[ns:Customers!2!ns:CompanyName!element],
> [Customers1].[CustomerID] as [ns:Customers!2!ns:CustomerID!hide]
> FROM
> (select
> [Customers].[CompanyName] as [CompanyName],
> [Customers].[CustomerID] as [CustomerID]
> from [Customers])
> as [Customers1]
> Order By [ns:Sample!1!!element] , [ns:Customers!2!ns:CustomerID!hide]
, Tag[vbcol=seagreen]
> FOR XML EXPLICIT
> "Michael Rys [MSFT]" wrote:
the[vbcol=seagreen]
[vbcol=seagreen]
with a[vbcol=seagreen]

Generating a namespace prefix with For XML Explicit

Hello,
How do I get my FOR XML explicit to qualify the returned elements with a
namespace prefix?
I want:
<s1:NewOrderLine xmlns:s1 ="www.company.com">
<s1:Quantity>23</s1:Quantity>
</s1:NewOrderLine>
not:
<NewOrderLine>
<Quantity>23</Quantity>
<NewOrderLine>
Thanks -
SimonIn SQL Server 2000, you need to do it explicitly add them.
Something like (if you are using auto mode):
select a as "s1:a", 'uri:example' as "xmlns:s1"
from t as "s1:t"
for xml auto
if you want to make element-centric mappings, you will need to use the
EXPLICIT mode.
Note in SQL Server 2005, there will be an easier way to specify XML
namespaces using the WITH XMLNAMESPACES clause.
Best regards
Michael
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:418B0654-6075-4FB6-8C2C-845B65E1E221@.microsoft.com...
> Hello,
> How do I get my FOR XML explicit to qualify the returned elements with a
> namespace prefix?
> I want:
> <s1:NewOrderLine xmlns:s1 ="www.company.com">
> <s1:Quantity>23</s1:Quantity>
> </s1:NewOrderLine>
> not:
> <NewOrderLine>
> <Quantity>23</Quantity>
> <NewOrderLine>
> Thanks -
> Simon
>|||Thanks for the reply, Michael. I wonder if you can elaborate on the EXPLICIT
mode option - with the query below I can get these results, but as you can
see, what I don't have is the 'xmlns:s1 ="www.company.com" in the root
element. Is it possible to change the query below to get that, or,
alternately, is it possible to load this result into an XMLDocument and then
add the namespace declaration?
<ns:SimpleTest>
<ns:Customers>
<ns:CompanyName>Alfreds Futterkiste</ns:CompanyName>
</ns:Customers>
</ns:SimpleTest>
from query:
select 1 as Tag,
Null as Parent,
Null as [ns:Sample!1!!element],
Null as [ns:Customers!2!ns:CompanyName!element],
Null as [ns:Customers!2!ns:CustomerID!hide]
UNION ALL
select 2 as Tag,
1 as Parent ,
Null as [ns:Sample!1!!element],
[Customers1].[CompanyName] as [ns:Customers!2!ns:CompanyName!element],
[Customers1].[CustomerID] as [ns:Customers!2!ns:CustomerID!hide]
FROM
(select
[Customers].[CompanyName] as [CompanyName],
[Customers].[CustomerID] as [CustomerID]
from [Customers])
as [Customers1]
Order By [ns:Sample!1!!element] , [ns:Customers!2!ns:CustomerID!hide] , Tag
FOR XML EXPLICIT
"Michael Rys [MSFT]" wrote:

> In SQL Server 2000, you need to do it explicitly add them.
> Something like (if you are using auto mode):
> select a as "s1:a", 'uri:example' as "xmlns:s1"
> from t as "s1:t"
> for xml auto
> if you want to make element-centric mappings, you will need to use the
> EXPLICIT mode.
> Note in SQL Server 2005, there will be an easier way to specify XML
> namespaces using the WITH XMLNAMESPACES clause.
> Best regards
> Michael
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:418B0654-6075-4FB6-8C2C-845B65E1E221@.microsoft.com...
>
>|||Simon,
I think the trick you are looking for is to treat xmlns as an
attribute:
Here is an example based on your request:
select 1 as tag, null as parent,
'www.company.com' as [s1:NewOrderLine!1!xmlns:s1],
null as [s1:Quantity!2]
UNION
select 2 as tag, 1 as parent,
'www.company.com' as [s1:NewOrderLine!1!xmlns:s1],
23 as [s1:Quantity!2]
ORDER BY TAG,PARENT
FOR XML EXPLICIT
This would produce the following:
<s1:NewOrderLine xmlns:s1="www.company.com">
<s1:Quantity>23</s1:Quantity>
</s1:NewOrderLine>
HTH,
Todd
Simon wrote:
> Thanks for the reply, Michael. I wonder if you can elaborate on the
EXPLICIT
> mode option - with the query below I can get these results, but as
you can
> see, what I don't have is the 'xmlns:s1 ="www.company.com" in the
root
> element. Is it possible to change the query below to get that, or,
> alternately, is it possible to load this result into an XMLDocument
and then
> add the namespace declaration?
> <ns:SimpleTest>
> <ns:Customers>
> <ns:CompanyName>Alfreds Futterkiste</ns:CompanyName>
> </ns:Customers>
> </ns:SimpleTest>
> from query:
> select 1 as Tag,
> Null as Parent,
> Null as [ns:Sample!1!!element],
> Null as [ns:Customers!2!ns:CompanyName!element],
> Null as [ns:Customers!2!ns:CustomerID!hide]
> UNION ALL
> select 2 as Tag,
> 1 as Parent ,
> Null as [ns:Sample!1!!element],
> [Customers1].[CompanyName] as
[ns:Customers!2!ns:CompanyName!element],

> [Customers1].[CustomerID] as [ns:Customers!2!ns:CustomerID!hide]
> FROM
> (select
> [Customers].[CompanyName] as [CompanyName],
> [Customers].[CustomerID] as [CustomerID]
> from [Customers])
> as [Customers1]
> Order By [ns:Sample!1!!element] , [ns:Customers!2!ns:CustomerID!hide]
, Tag
> FOR XML EXPLICIT
> "Michael Rys [MSFT]" wrote:
>
the
with a