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 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

Generarting XML based on Schema in SQLSERVER 2005

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

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

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

|||

Todd Pfleiger wrote:

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

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

thanks for your help

Generarting XML based on Schema in SQLSERVER 2005

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

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

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

|||

Todd Pfleiger wrote:

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

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

thanks for your help