java指定sql生成xml_sql server 中生成xml

ProblemWith XML being used in so many application

and data exchange capacities, extracting XML from SQL Server should not

be a challenge.  Yet, some organizations are building complex

applications to do so and overlooking some of the native SQL Server

features.  If the data tier has a viable option to extract XML in a

native format, should that option be considered?

SolutionYes - Should be the resounding answer.  The trusted SELECT statement (SQL Server 2000and SQL Server 2005) includes a FOR XML option (SQL Server 2000 and SQL Server 2005)

with the ability to extract XML in a few different formats.  Each of

these options offers a different means to format the XML.

Why would I want to use the FOR XML options?

One of the strongest reasons to use one of the FOR XML options is

that they are a simple extension to the trusted SELECT statement.

Regardless of the application, some sort of SELECT statement needs to be

built, so adding the XML parameters is a no brainier.  Another

consideration for choosing the FOR XML statement is that the SELECT

statement can be called from a stored procedure, DTS\SSIS package or a

script.  Finally, the FOR XML clause is supported in both SQL Server

2000 (with the exception of the XML PATH option) and 2005.

What are the differences between the FOR XML options?

ID

Option

Description

1

FOR XML AUTO

Simple nested tree of XML with each column being represented as a single element

2

FOR XML RAW

Each row in the result set is transformed into generic element tag

3

FOR XML EXPLICIT

A predefined XML format is created for the result set

4

FOR XML PATH

Much of the same functionality as the EXPLICIT mode, but the elements and attributes can be built with XPATH like syntax

This option is not available in SQL Server 2000.

FOR XML AUTO

SELECT TOP 1 a.au_lname AS 'AuthorLastName',

a.au_fname AS 'AuthorFirstName',

t.title AS 'Title',

t.pubdate AS 'PublicationDate'

FROM dbo.Authors a

INNER JOIN dbo.TitleAuthor ta

ON a.au_id = ta.au_id

INNER JOIN dbo.Titles t

ON ta.title_id = t.title_id

WHERE a.state = 'CA'

FOR XML AUTO

SELECT TOP 1 c.CourseName,

c.CourseDesc,

s.SectionTitle

FROM Course c

INNER JOIN Section s

ON c.CourseID = s.CourseID

FOR XML AUTO

AuthorLastName="Green" AuthorFirstName="Marjorie">

PublicationDate="1991-06-12T00:00:00"/>

FOR XML RAW

SELECT TOP 1 a.au_lname AS 'AuthorLastName',

a.au_fname AS 'AuthorFirstName',

t.title AS 'Title',

t.pubdate AS 'PublicationDate'

FROM dbo.Authors a

INNER JOIN dbo.TitleAuthor ta

ON a.au_id = ta.au_id

INNER JOIN dbo.Titles t

ON ta.title_id = t.title_id

WHERE a.state = 'CA'

FOR XML RAW

SELECT TOP 1 c.CourseName,

c.CourseDesc,

s.SectionTitle

FROM Course c

INNER JOIN Section s

ON c.CourseID = s.CourseID

FOR XML RAW

AuthorLastName="Green" AuthorFirstName="Marjorie" Title="The Busy

Executive's Database Guide"

PublicationDate="1991-06-12T00:00:00"/>

FOR XML EXPLICIT

SELECT 1 AS Tag,

NULL AS Parent,

t.title AS [Title!1!TitleName],

NULL AS [LastName!2!AuthorLastName]

FROM dbo.Authors a

INNER JOIN dbo.TitleAuthor ta

ON a.au_id = ta.au_id

INNER JOIN dbo.Titles t

ON ta.title_id = t.title_id

WHERE a.state = 'CA'

UNION ALL

SELECT 2 AS Tag,

1 AS Parent,

t.title,

a.au_lname

FROM dbo.Authors a

INNER JOIN dbo.TitleAuthor ta

ON a.au_id = ta.au_id

INNER JOIN dbo.Titles t

ON ta.title_id = t.title_id

WHERE a.state = 'CA'

ORDER BY [Title!1!TitleName], [LastName!2!AuthorLastName]

FOR XML EXPLICIT

SELECT 1 AS Tag,

NULL AS Parent,

c.CourseName AS [Course!1!CourseName],

NULL AS [Section!2!SectionTitle]

FROM Course c

INNER JOIN Section s

ON c.CourseID = s.CourseID

WHERE c.CourseID = 1

UNION ALL

SELECT 2 AS Tag,

1 AS Parent,

c.CourseName,

s.SectionTitle

FROM Course c

INNER JOIN Section s

ON c.CourseID = s.CourseID

WHERE c.CourseID = 1

FOR XML EXPLICIT

...

FOR XML PATH

SQL Server 2000

Not available

SELECT c.CourseName,

c.CourseDesc,

s.SectionTitle

FROM Course c

INNER JOIN Section s

ON c.CourseID = s.CourseID

FOR XML PATH

Not available

DBMS-101

Database fundamentals

MWF-8:00 AM

Next Steps

As you are faced with requirements to extract XML from your current

systems, consider the SELECT FOR XML options as a viable means to

complete the task.

If you have not had time to explore the many capabilities of

XML, start with these simple examples and begin to make them more

complex to meet your needs.

Stay tuned for more of SQL Server's native XML capabilities