generating scripts in SqlServer 2005?

Okay, in Sql 2000, when I wanted to generate scripts, I could easily have
both the create and drop commands scripted into the same document with one
step. How do I do that with Sql 2005. I can generate the script for
creating the table and for dropping the table, but I cannot see the flag
where I can have both scripted at the same time.> step. How do I do that with Sql 2005.
Please use the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1sql

Generating a correct xpath_namespaces

I want to open a XML document with a xmlns declaration, i open the xml with:
-- Open the XML doc
EXEC sp_xml_preparedocument @.itemHandle OUTPUT, @.xml, '<PriceList xmlns:maxbo="http://maxbo.no/schema/PriceList.xsd" />'

Then i try to select somthing (this returns nothing)
select * from
OPENXML(@.itemHandle, N'/PriceList')

Here are the first few rows.
<?xml version="1.0" encoding="iso-8859-1"?>
<PriceList xmlns="http://maxbo.no/schema/PriceList.xsd">.....

The following code works fine, so how do i generate the xmlns for the above scenario? (it seems obvious to me that the xpath_namespace declartation is somhow faulty)

I want to open a XML document with a xmlns declaration, i open the xml with:
-- Open the XML doc
EXEC sp_xml_preparedocument @.itemHandle OUTPUT, @.xml
Then i try to select somthing (this returns nothing)
select * from
OPENXML(@.itemHandle, N'/PriceList')
Here are the first few rows.
<?xml version="1.0" encoding="iso-8859-1"?>
<PriceList>....

You need to provide the namespace/prefix binding to the third argument as you do above, but you need to use a prefix to bind the namespace URI to AND use it in the query. Default namespaces are unfortunately not picked up.

Try:

EXEC sp_xml_preparedocument @.itemHandle OUTPUT, @.xml, '<PriceList xmlns:maxbo="http://maxbo.no/schema/PriceList.xsd" />'
select * from
OPENXML(@.itemHandle, N'/maxbo:PriceList')

Best regards

Michael

|||

That works, although I have to prefix every item in the list like this:

updateDate varchar(50) '/maxbo:PriceList/maxbo:Update/text()',

But at least it works. If you have an easier way of accessing subitems, I would be gratefull, since some paths can become quite long.

Generate XML from SQL 2000 tables

Hi,

I am a newbie to XML. I am trying to generate a customised format of XML document from SQL 2000 table. This document is something like an acknowledgment reply. Does it mean I have to create a table structure similar to the format of the XML document to facilitate the generation of the desired format?

Any kind souls, please help.

Thanks.Did you look into

FOR XML

Operator?|||In this case, am I correct to say I need to have a table structure that maps (which currently does not exist) to the hierarchy of the XML acknowledgement first. Pardon my ignorance. ;D
===================
Originally posted by Brett Kaiser
Did you look into

FOR XML

Operator?|||Try this in QA

USE Northwind
GO
SELECT * FROM Orders FOR XML AUTO
GO

Generate XML document problem

I'm having the same problem as described below. Although this was
posted on usenet a few years ago, a solution was not posted, and I
can't find one on the internet anywhere. Does anyone have any idea as
to how I might workaround this? Note that I am not sorting on any of
the varchar fields that are causing the problem.
Thanks,
Aaron

> Here is the problem,
> I need to generate an XML document using FOR XML Explicit. My select
> works fine but I run into a problem when I try to do an order by with
> a row that is greater than 8094 bytes. SQL Server outputs the error
> "Cannot sort a row of size 96555, which is greater than the allowable
> maximum of 8094".
> The order by is very important in my query, because without it. I will
> not get the XML document in the correct schema.
> I have researched this issue and have come to the conclusion there is
> not a way I know of to get around this limitation. I tried using the
> query hint "Robust Plan" and "Force Order" and that did not work.
> A solution that I thought of, but don't know if it will work and how
> to do in SQL server is to first generate the XML schema without the
> TEXT column fields and then do another query to load the text columns
> into the XML document.
> Can I and how do I do all of this in a Stored Procedure?
> Or is there a better workaround for my problem?
> Thanks in advance
>
> --
> B. HarilalHi Aaron
Can you please provide some more information? Is it the order by clause that
may contain too much data? Or the rows that you sort?
It would be very useful if you can provide a simple repro.
Thanks
Michael
"AaronS" <gcsdba1@.yahoo.com> wrote in message
news:1140618933.285133.96480@.z14g2000cwz.googlegroups.com...
> I'm having the same problem as described below. Although this was
> posted on usenet a few years ago, a solution was not posted, and I
> can't find one on the internet anywhere. Does anyone have any idea as
> to how I might workaround this? Note that I am not sorting on any of
> the varchar fields that are causing the problem.
> Thanks,
> Aaron
>
>|||Here's an example of what the query is trying to do. I know this query
is not logically correct, but hopefully it gives you an idea of what
the actual query is doing.
SELECT
1 as Tag,
NULL as Parent,
reportID as [reportData!1!id],
NULL as [comments!5!reportCommentID!hide],
NULL as [comment!15!details],
NULL as [Notes!10!reportCaseID!hide],
NULL as [Note!30!noteDesc]
FROM report
UNION ALL
SELECT
15 as Tag,
5 as Parent,
NULL as [reportData!1!id],
reportID as [comments!5!reportCommentID!hide],
details as [comment!15!details] -- varchar(8000)
NULL as [Notes!10!reportCaseID!hide],
NULL as [Note!30!noteDesc]
FROM comment
UNION ALL
SELECT
30 as Tag,
10 as Parent,
NULL as [reportData!1!id],
NULL as [comments!5!reportCommentID!hide],
NULL as [comment!15!details],
reportID as [Notes!10!reportCaseID!hide],
NoteDesc as [Note!30!noteDesc] -- varchar(8000)
FROM note
-- Note that the sort is not on any of the "long" varchar fields
ORDER BY
[reportData!1!id],
[comments!5!reportCommentID!hide]
[Notes!10!reportCaseID!hide]|||Hi Michael,
I realize my SQL example is very crude (especially since I left out any
join conditions), but does it get the idea across about where the
problem lies? It appears to be the rows I am attempting sort that
contain too much data for SQL Server to handle. The columns specified
in the order by clause do not appear to be the culprit as this error
occurs only when the selected data itself exceeds the SQL Server limit
of 8094 characters. Can you (or anyone else reading this) provide any
insight? I would really appreciate it. I'm at a loss of where to go
from here except to start truncating the lengthy varchar strings. I
don't even know if I have the option to do that.
Aaron

Generate XML document problem

I'm having the same problem as described below. Although this was
posted on usenet a few years ago, a solution was not posted, and I
can't find one on the internet anywhere. Does anyone have any idea as
to how I might workaround this? Note that I am not sorting on any of
the varchar fields that are causing the problem.
Thanks,
Aaron

> Here is the problem,
> I need to generate an XML document using FOR XML Explicit. My select
> works fine but I run into a problem when I try to do an order by with
> a row that is greater than 8094 bytes. SQL Server outputs the error
> "Cannot sort a row of size 96555, which is greater than the allowable
> maximum of 8094".
> The order by is very important in my query, because without it. I will
> not get the XML document in the correct schema.
> I have researched this issue and have come to the conclusion there is
> not a way I know of to get around this limitation. I tried using the
> query hint "Robust Plan" and "Force Order" and that did not work.
> A solution that I thought of, but don't know if it will work and how
> to do in SQL server is to first generate the XML schema without the
> TEXT column fields and then do another query to load the text columns
> into the XML document.
> Can I and how do I do all of this in a Stored Procedure?
> Or is there a better workaround for my problem?
> Thanks in advance
>
> --
> B. Harilal
Hi Aaron
Can you please provide some more information? Is it the order by clause that
may contain too much data? Or the rows that you sort?
It would be very useful if you can provide a simple repro.
Thanks
Michael
"AaronS" <gcsdba1@.yahoo.com> wrote in message
news:1140618933.285133.96480@.z14g2000cwz.googlegro ups.com...
> I'm having the same problem as described below. Although this was
> posted on usenet a few years ago, a solution was not posted, and I
> can't find one on the internet anywhere. Does anyone have any idea as
> to how I might workaround this? Note that I am not sorting on any of
> the varchar fields that are causing the problem.
> Thanks,
> Aaron
>
|||Here's an example of what the query is trying to do. I know this query
is not logically correct, but hopefully it gives you an idea of what
the actual query is doing.
SELECT
1 as Tag,
NULL as Parent,
reportID as [reportData!1!id],
NULL as [comments!5!reportCommentID!hide],
NULL as [comment!15!details],
NULL as [Notes!10!reportCaseID!hide],
NULL as [Note!30!noteDesc]
FROM report
UNION ALL
SELECT
15 as Tag,
5 as Parent,
NULL as [reportData!1!id],
reportID as [comments!5!reportCommentID!hide],
details as [comment!15!details] -- varchar(8000)
NULL as [Notes!10!reportCaseID!hide],
NULL as [Note!30!noteDesc]
FROM comment
UNION ALL
SELECT
30 as Tag,
10 as Parent,
NULL as [reportData!1!id],
NULL as [comments!5!reportCommentID!hide],
NULL as [comment!15!details],
reportID as [Notes!10!reportCaseID!hide],
NoteDesc as [Note!30!noteDesc] -- varchar(8000)
FROM note
-- Note that the sort is not on any of the "long" varchar fields
ORDER BY
[reportData!1!id],
[comments!5!reportCommentID!hide]
[Notes!10!reportCaseID!hide]
|||Hi Michael,
I realize my SQL example is very crude (especially since I left out any
join conditions), but does it get the idea across about where the
problem lies? It appears to be the rows I am attempting sort that
contain too much data for SQL Server to handle. The columns specified
in the order by clause do not appear to be the culprit as this error
occurs only when the selected data itself exceeds the SQL Server limit
of 8094 characters. Can you (or anyone else reading this) provide any
insight? I would really appreciate it. I'm at a loss of where to go
from here except to start truncating the lengthy varchar strings. I
don't even know if I have the option to do that.
Aaron

Generate SQL Server tables from an XSD

Hi, I need to generate tables from an XSD document. Does anyone knows about
a way to do this? ThanksHi,
You can use Xml Bulkload component to generate the tables from a supplied
annotated schema file.You have to set SchemaGen property to True.
Check out paragraph E from the below link for an example on how to do it:
http://msdn.microsoft.com/library/d...>
h_SQLXML.asp
Best regards,
Monica Frintu
"Daniele Romagnoli" wrote:

> Hi, I need to generate tables from an XSD document. Does anyone knows abou
t
> a way to do this? Thanks
>
>

Generate a normalized XML doc from SQL Server

I'm trying to generate a normalized XML document out of SQL server
that reflects the data
structure of a table.
Eg. This is what I would like to get
<table name='MtFeedback'>
<field name="MtFeedbackIy" type="int"/>
<field name="Title" type="varchar" size="50"/>
<field name="FirstName" type="varchar" size="50"/>
<field name="Surname" type="varchar" size="50"/>
<field name="insertedon" type="datetime"/>
</table>
I have tried two ways of and these are the results. The 1st technique
is close
select
1 as Tag,
null as Parent,
'' as [table!1],
null as [field!2!table-name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
UNION ALL
select
2 as Tag,
1 as Parent,
null as [table!1],
i.TABLE_NAME as [field!2!table-name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = 'MtFeedback'
FOR XML EXPLICIT
<table>
<field table-name="MtFeedback" name="MtFeedbackIy" type="int"/>
<field table-name="MtFeedback" name="Title" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="FirstName" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="Surname" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="insertedon" type="datetime"/>
</table>
The 2nd technique fails totally.
select
1 as Tag,
null as Parent,
i.TABLE_NAME as [table!1!name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
from
information_schema.columns i
UNION ALL
select
2 as Tag,
1 as Parent,
null as [table!1!name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = 'MtFeedback'
FOR XML EXPLICIT
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="categories"/>
<table name="categories"/>
<table name="products"/>
<table name="products"/>
<table name="products"/>
<table name="descriptions"/>
<table name="descriptions"/>
<table name="descriptions"/>
<table name="syssegments"/>
<table name="syssegments"/>
<table name="syssegments"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties">
<field name="MtFeedbackIy" type="int"/>
<field name="Title" type="varchar" size="50"/>
<field name="FirstName" type="varchar" size="50"/>
<field name="Surname" type="varchar" size="50"/>
<field name="insertedon" type="datetime"/>
</table>
Cheers Daviddeclare @.tb sysname
set @.tb='MtFeedback'
select
1 as Tag,
null as Parent,
@.tb as [table!1!name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
UNION ALL
select
2 as Tag,
1 as Parent,
i.TABLE_NAME as [table!1!name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = @.tb
FOR XML EXPLICIT
"muesliflakes" <muesliflakes@.yahoo.com.au> wrote in message
news:8deb82de.0406011916.1e8194b3@.posting.google.com...
> I'm trying to generate a normalized XML document out of SQL server
> that reflects the data
> structure of a table.
> Eg. This is what I would like to get
> <table name='MtFeedback'>
> <field name="MtFeedbackIy" type="int"/>
> <field name="Title" type="varchar" size="50"/>
> <field name="FirstName" type="varchar" size="50"/>
> <field name="Surname" type="varchar" size="50"/>
> <field name="insertedon" type="datetime"/>
> </table>
> I have tried two ways of and these are the results. The 1st technique
> is close
> select
> 1 as Tag,
> null as Parent,
> '' as [table!1],
> null as [field!2!table-name],
> null as [field!2!name],
> null as [field!2!type],
> null as [field!2!size]
> UNION ALL
> select
> 2 as Tag,
> 1 as Parent,
> null as [table!1],
> i.TABLE_NAME as [field!2!table-name],
> i.COLUMN_NAME as [field!2!name],
> i.DATA_TYPE as [field!2!type],
> i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
> from
> information_schema.columns i
> where
> table_name = 'MtFeedback'
> FOR XML EXPLICIT
> <table>
> <field table-name="MtFeedback" name="MtFeedbackIy" type="int"/>
> <field table-name="MtFeedback" name="Title" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="FirstName" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="Surname" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="insertedon" type="datetime"/>
> </table>
> The 2nd technique fails totally.
> select
> 1 as Tag,
> null as Parent,
> i.TABLE_NAME as [table!1!name],
> null as [field!2!name],
> null as [field!2!type],
> null as [field!2!size]
> from
> information_schema.columns i
> UNION ALL
> select
> 2 as Tag,
> 1 as Parent,
> null as [table!1!name],
> i.COLUMN_NAME as [field!2!name],
> i.DATA_TYPE as [field!2!type],
> i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
> from
> information_schema.columns i
> where
> table_name = 'MtFeedback'
> FOR XML EXPLICIT
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="categories"/>
> <table name="categories"/>
> <table name="products"/>
> <table name="products"/>
> <table name="products"/>
> <table name="descriptions"/>
> <table name="descriptions"/>
> <table name="descriptions"/>
> <table name="syssegments"/>
> <table name="syssegments"/>
> <table name="syssegments"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties">
> <field name="MtFeedbackIy" type="int"/>
> <field name="Title" type="varchar" size="50"/>
> <field name="FirstName" type="varchar" size="50"/>
> <field name="Surname" type="varchar" size="50"/>
> <field name="insertedon" type="datetime"/>
> </table>
> Cheers David

Generate a normalized XML doc from SQL Server

I'm trying to generate a normalized XML document out of SQL server
that reflects the data
structure of a table.
Eg. This is what I would like to get
<table name='MtFeedback'>
<field name="MtFeedbackIy" type="int"/>
<field name="Title" type="varchar" size="50"/>
<field name="FirstName" type="varchar" size="50"/>
<field name="Surname" type="varchar" size="50"/>
<field name="insertedon" type="datetime"/>
</table>
I have tried two ways of and these are the results. The 1st technique
is close
select
1 as Tag,
null as Parent,
'' as [table!1],
null as [field!2!table-name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
UNION ALL
select
2 as Tag,
1 as Parent,
null as [table!1],
i.TABLE_NAME as [field!2!table-name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = 'MtFeedback'
FOR XML EXPLICIT
<table>
<field table-name="MtFeedback" name="MtFeedbackIy" type="int"/>
<field table-name="MtFeedback" name="Title" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="FirstName" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="Surname" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="insertedon" type="datetime"/>
</table>
The 2nd technique fails totally.
select
1 as Tag,
null as Parent,
i.TABLE_NAME as [table!1!name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
from
information_schema.columns i
UNION ALL
select
2 as Tag,
1 as Parent,
null as [table!1!name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = 'MtFeedback'
FOR XML EXPLICIT
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="categories"/>
<table name="categories"/>
<table name="products"/>
<table name="products"/>
<table name="products"/>
<table name="descriptions"/>
<table name="descriptions"/>
<table name="descriptions"/>
<table name="syssegments"/>
<table name="syssegments"/>
<table name="syssegments"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties">
<field name="MtFeedbackIy" type="int"/>
<field name="Title" type="varchar" size="50"/>
<field name="FirstName" type="varchar" size="50"/>
<field name="Surname" type="varchar" size="50"/>
<field name="insertedon" type="datetime"/>
</table>
Cheers Daviddeclare @.tb sysname
set @.tb='MtFeedback'
select
1 as Tag,
null as Parent,
@.tb as [table!1!name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
UNION ALL
select
2 as Tag,
1 as Parent,
i.TABLE_NAME as [table!1!name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = @.tb
FOR XML EXPLICIT
"muesliflakes" <muesliflakes@.yahoo.com.au> wrote in message
news:8deb82de.0406011916.1e8194b3@.posting.google.com...
> I'm trying to generate a normalized XML document out of SQL server
> that reflects the data
> structure of a table.
> Eg. This is what I would like to get
> <table name='MtFeedback'>
> <field name="MtFeedbackIy" type="int"/>
> <field name="Title" type="varchar" size="50"/>
> <field name="FirstName" type="varchar" size="50"/>
> <field name="Surname" type="varchar" size="50"/>
> <field name="insertedon" type="datetime"/>
> </table>
> I have tried two ways of and these are the results. The 1st technique
> is close
> select
> 1 as Tag,
> null as Parent,
> '' as [table!1],
> null as [field!2!table-name],
> null as [field!2!name],
> null as [field!2!type],
> null as [field!2!size]
> UNION ALL
> select
> 2 as Tag,
> 1 as Parent,
> null as [table!1],
> i.TABLE_NAME as [field!2!table-name],
> i.COLUMN_NAME as [field!2!name],
> i.DATA_TYPE as [field!2!type],
> i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
> from
> information_schema.columns i
> where
> table_name = 'MtFeedback'
> FOR XML EXPLICIT
> <table>
> <field table-name="MtFeedback" name="MtFeedbackIy" type="int"/>
> <field table-name="MtFeedback" name="Title" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="FirstName" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="Surname" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="insertedon" type="datetime"/>
> </table>
> The 2nd technique fails totally.
> select
> 1 as Tag,
> null as Parent,
> i.TABLE_NAME as [table!1!name],
> null as [field!2!name],
> null as [field!2!type],
> null as [field!2!size]
> from
> information_schema.columns i
> UNION ALL
> select
> 2 as Tag,
> 1 as Parent,
> null as [table!1!name],
> i.COLUMN_NAME as [field!2!name],
> i.DATA_TYPE as [field!2!type],
> i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
> from
> information_schema.columns i
> where
> table_name = 'MtFeedback'
> FOR XML EXPLICIT
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="categories"/>
> <table name="categories"/>
> <table name="products"/>
> <table name="products"/>
> <table name="products"/>
> <table name="descriptions"/>
> <table name="descriptions"/>
> <table name="descriptions"/>
> <table name="syssegments"/>
> <table name="syssegments"/>
> <table name="syssegments"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties">
> <field name="MtFeedbackIy" type="int"/>
> <field name="Title" type="varchar" size="50"/>
> <field name="FirstName" type="varchar" size="50"/>
> <field name="Surname" type="varchar" size="50"/>
> <field name="insertedon" type="datetime"/>
> </table>
> Cheers David

Generate a normalized XML doc from SQL Server

I'm trying to generate a normalized XML document out of SQL server
that reflects the data
structure of a table.
Eg. This is what I would like to get
<table name='MtFeedback'>
<field name="MtFeedbackIy" type="int"/>
<field name="Title" type="varchar" size="50"/>
<field name="FirstName" type="varchar" size="50"/>
<field name="Surname" type="varchar" size="50"/>
<field name="insertedon" type="datetime"/>
</table>
I have tried two ways of and these are the results. The 1st technique
is close
select
1 as Tag,
null as Parent,
'' as [table!1],
null as [field!2!table-name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
UNION ALL
select
2 as Tag,
1 as Parent,
null as [table!1],
i.TABLE_NAME as [field!2!table-name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = 'MtFeedback'
FOR XML EXPLICIT
<table>
<field table-name="MtFeedback" name="MtFeedbackIy" type="int"/>
<field table-name="MtFeedback" name="Title" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="FirstName" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="Surname" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="insertedon" type="datetime"/>
</table>
The 2nd technique fails totally.
select
1 as Tag,
null as Parent,
i.TABLE_NAME as [table!1!name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
from
information_schema.columns i
UNION ALL
select
2 as Tag,
1 as Parent,
null as [table!1!name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = 'MtFeedback'
FOR XML EXPLICIT
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="categories"/>
<table name="categories"/>
<table name="products"/>
<table name="products"/>
<table name="products"/>
<table name="descriptions"/>
<table name="descriptions"/>
<table name="descriptions"/>
<table name="syssegments"/>
<table name="syssegments"/>
<table name="syssegments"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties">
<field name="MtFeedbackIy" type="int"/>
<field name="Title" type="varchar" size="50"/>
<field name="FirstName" type="varchar" size="50"/>
<field name="Surname" type="varchar" size="50"/>
<field name="insertedon" type="datetime"/>
</table>
Cheers David
declare @.tb sysname
set @.tb='MtFeedback'
select
1 as Tag,
null as Parent,
@.tb as [table!1!name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
UNION ALL
select
2 as Tag,
1 as Parent,
i.TABLE_NAME as [table!1!name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = @.tb
FOR XML EXPLICIT
"muesliflakes" <muesliflakes@.yahoo.com.au> wrote in message
news:8deb82de.0406011916.1e8194b3@.posting.google.c om...
> I'm trying to generate a normalized XML document out of SQL server
> that reflects the data
> structure of a table.
> Eg. This is what I would like to get
> <table name='MtFeedback'>
> <field name="MtFeedbackIy" type="int"/>
> <field name="Title" type="varchar" size="50"/>
> <field name="FirstName" type="varchar" size="50"/>
> <field name="Surname" type="varchar" size="50"/>
> <field name="insertedon" type="datetime"/>
> </table>
> I have tried two ways of and these are the results. The 1st technique
> is close
> select
> 1 as Tag,
> null as Parent,
> '' as [table!1],
> null as [field!2!table-name],
> null as [field!2!name],
> null as [field!2!type],
> null as [field!2!size]
> UNION ALL
> select
> 2 as Tag,
> 1 as Parent,
> null as [table!1],
> i.TABLE_NAME as [field!2!table-name],
> i.COLUMN_NAME as [field!2!name],
> i.DATA_TYPE as [field!2!type],
> i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
> from
> information_schema.columns i
> where
> table_name = 'MtFeedback'
> FOR XML EXPLICIT
> <table>
> <field table-name="MtFeedback" name="MtFeedbackIy" type="int"/>
> <field table-name="MtFeedback" name="Title" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="FirstName" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="Surname" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="insertedon" type="datetime"/>
> </table>
> The 2nd technique fails totally.
> select
> 1 as Tag,
> null as Parent,
> i.TABLE_NAME as [table!1!name],
> null as [field!2!name],
> null as [field!2!type],
> null as [field!2!size]
> from
> information_schema.columns i
> UNION ALL
> select
> 2 as Tag,
> 1 as Parent,
> null as [table!1!name],
> i.COLUMN_NAME as [field!2!name],
> i.DATA_TYPE as [field!2!type],
> i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
> from
> information_schema.columns i
> where
> table_name = 'MtFeedback'
> FOR XML EXPLICIT
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="categories"/>
> <table name="categories"/>
> <table name="products"/>
> <table name="products"/>
> <table name="products"/>
> <table name="descriptions"/>
> <table name="descriptions"/>
> <table name="descriptions"/>
> <table name="syssegments"/>
> <table name="syssegments"/>
> <table name="syssegments"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties">
> <field name="MtFeedbackIy" type="int"/>
> <field name="Title" type="varchar" size="50"/>
> <field name="FirstName" type="varchar" size="50"/>
> <field name="Surname" type="varchar" size="50"/>
> <field name="insertedon" type="datetime"/>
> </table>
> Cheers David

Generate a normalized XML doc from SQL Server

I'm trying to generate a normalized XML document out of SQL server
that reflects the data
structure of a table.

Eg. This is what I would like to get

<table name='MtFeedback'>
<field name="MtFeedbackIy" type="int"/>
<field name="Title" type="varchar" size="50"/>
<field name="FirstName" type="varchar" size="50"/>
<field name="Surname" type="varchar" size="50"/>
<field name="insertedon" type="datetime"/>
</table
I have tried two ways of and these are the results. The 1st technique
is close

select
1 as Tag,
null as Parent,
'' as [table!1],
null as [field!2!table-name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
UNION ALL
select
2 as Tag,
1 as Parent,
null as [table!1],
i.TABLE_NAME as [field!2!table-name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = 'MtFeedback'
FOR XML EXPLICIT

<table>
<field table-name="MtFeedback" name="MtFeedbackIy" type="int"/>
<field table-name="MtFeedback" name="Title" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="FirstName" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="Surname" type="varchar"
size="50"/>
<field table-name="MtFeedback" name="insertedon" type="datetime"/>
</table
The 2nd technique fails totally.

select
1 as Tag,
null as Parent,
i.TABLE_NAME as [table!1!name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
from
information_schema.columns i
UNION ALL
select
2 as Tag,
1 as Parent,
null as [table!1!name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = 'MtFeedback'
FOR XML EXPLICIT

<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="MtFeedback"/>
<table name="categories"/>
<table name="categories"/>
<table name="products"/>
<table name="products"/>
<table name="products"/>
<table name="descriptions"/>
<table name="descriptions"/>
<table name="descriptions"/>
<table name="syssegments"/>
<table name="syssegments"/>
<table name="syssegments"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="sysconstraints"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties"/>
<table name="dtproperties">
<field name="MtFeedbackIy" type="int"/>
<field name="Title" type="varchar" size="50"/>
<field name="FirstName" type="varchar" size="50"/>
<field name="Surname" type="varchar" size="50"/>
<field name="insertedon" type="datetime"/>
</table
Cheers Davedeclare @.tb sysname
set @.tb='MtFeedback'

select
1 as Tag,
null as Parent,
@.tb as [table!1!name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
UNION ALL
select
2 as Tag,
1 as Parent,
i.TABLE_NAME as [table!1!name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = @.tb
FOR XML EXPLICIT

"muesliflakes" <muesliflakes@.yahoo.com.au> wrote in message
news:8deb82de.0406011915.399be4df@.posting.google.c om...
> I'm trying to generate a normalized XML document out of SQL server
> that reflects the data
> structure of a table.
> Eg. This is what I would like to get
> <table name='MtFeedback'>
> <field name="MtFeedbackIy" type="int"/>
> <field name="Title" type="varchar" size="50"/>
> <field name="FirstName" type="varchar" size="50"/>
> <field name="Surname" type="varchar" size="50"/>
> <field name="insertedon" type="datetime"/>
> </table>
> I have tried two ways of and these are the results. The 1st technique
> is close
> select
> 1 as Tag,
> null as Parent,
> '' as [table!1],
> null as [field!2!table-name],
> null as [field!2!name],
> null as [field!2!type],
> null as [field!2!size]
> UNION ALL
> select
> 2 as Tag,
> 1 as Parent,
> null as [table!1],
> i.TABLE_NAME as [field!2!table-name],
> i.COLUMN_NAME as [field!2!name],
> i.DATA_TYPE as [field!2!type],
> i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
> from
> information_schema.columns i
> where
> table_name = 'MtFeedback'
> FOR XML EXPLICIT
> <table>
> <field table-name="MtFeedback" name="MtFeedbackIy" type="int"/>
> <field table-name="MtFeedback" name="Title" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="FirstName" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="Surname" type="varchar"
> size="50"/>
> <field table-name="MtFeedback" name="insertedon" type="datetime"/>
> </table>
> The 2nd technique fails totally.
> select
> 1 as Tag,
> null as Parent,
> i.TABLE_NAME as [table!1!name],
> null as [field!2!name],
> null as [field!2!type],
> null as [field!2!size]
> from
> information_schema.columns i
> UNION ALL
> select
> 2 as Tag,
> 1 as Parent,
> null as [table!1!name],
> i.COLUMN_NAME as [field!2!name],
> i.DATA_TYPE as [field!2!type],
> i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
> from
> information_schema.columns i
> where
> table_name = 'MtFeedback'
> FOR XML EXPLICIT
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="MtFeedback"/>
> <table name="categories"/>
> <table name="categories"/>
> <table name="products"/>
> <table name="products"/>
> <table name="products"/>
> <table name="descriptions"/>
> <table name="descriptions"/>
> <table name="descriptions"/>
> <table name="syssegments"/>
> <table name="syssegments"/>
> <table name="syssegments"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="sysconstraints"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties"/>
> <table name="dtproperties">
> <field name="MtFeedbackIy" type="int"/>
> <field name="Title" type="varchar" size="50"/>
> <field name="FirstName" type="varchar" size="50"/>
> <field name="Surname" type="varchar" size="50"/>
> <field name="insertedon" type="datetime"/>
> </table>
> Cheers Dave