SQLCMD and Output to an XML file issue

/*

Start sql cmd and type the following command:

*/

SELECT (

SELECT DepartmentID AS "DepartmentID",Name AS "DepartmentName", GroupName AS "GroupName"

FROM

HumanResources.Department

FOR XML PATH(‘Department’),TYPE)

FOR

XML PATH(‘Departments’),ROOT(‘Organizations’)

GO

/* The output becomes byte stream. Based on BOL, we need to turn on xml output. :XML ON.

The BOL is configusing here:

"XML output that is the result of a FOR XML clause is output, unformatted, in a continuous stream.

When you expect XML output, use the following command: :XML ON.

The GO command should not appear before the XML OFF command is issued because the XML OFF command switches sqlcmd back to row-oriented output."

Actulaly if we do not include GO before XML:OFF, we still get byte streams

*/

:

XML ON

SELECT

(

SELECT DepartmentID AS "DepartmentID",Name AS "DepartmentName", GroupName AS "GroupName"

FROM HumanResources.Department

FOR

XML PATH(‘Department’),TYPE)

FOR

XML PATH(‘Departments’),ROOT(‘Organizations’)

:

XML OFF

GO

/* The following command gives you valid xml: */

:

XML ON

SELECT

(

SELECT

DepartmentID AS "DepartmentID",Name AS "DepartmentName", GroupName AS "GroupName"

FROM

HumanResources.Department

FOR XML PATH(‘Department’),TYPE)

FOR

XML PATH(‘Departments’),ROOT(‘Organizations’)

GO

/*

If we assign the xml to a variable, we do not need the XML:ON command. To avoid the confusing, it’s better to assign it to a variable first

In sqlcmd window, run the following commmand:

*/

DECLARE

@xml xml

SET

@xml=(

SELECT

(

SELECT

DepartmentID AS "DepartmentID",Name AS "DepartmentName", GroupName AS "GroupName"

FROM

HumanResources.Department

FOR

XML PATH(‘Department’),TYPE)

FOR XML PATH(‘Departments’),ROOT(‘Organizations’))

SELECT

@xml

GO

Advertisements
This entry was posted in XML, XQuery and XPath. Bookmark the permalink.

One Response to SQLCMD and Output to an XML file issue

  1. Vanessa says:

    It was hard to find your blog in google. You should create some high PR contextual backlinks in order to rank your page.
    I know – writing articles is very time consuming, but contextual backlinks are the
    best type of backlinks. I know one cool tool that will help you to create unique,
    readable content in minute, just search in google – rewriter creates an unique article in a minute

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s