Save Xml Document Created by FOR XML with XmlNamespace

/*

When creating xml document by FOR XML PATH ( or AUTO, RAW etc), we can save the generated xml document to a xml data type variable by specify the TYPE directive. E.g.:

*/

DECLARE @Orders xml;

SET

@Orders=(

SELECT TOP 5 SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, SalesOrderNumber, PurchaseOrderNumber, AccountNumber, CustomerID, ContactID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID

FROM

Sales.SalesOrderHeader

FOR XML PATH (‘Order’), ROOT(‘Orders’), TYPE)

SELECT

@Orders

/*

FOR XML also supports namespace by specifying WITH XMLNAMESPACES. The following code generates the same xml as previous one with the name space http://schemas.blah.com/Orders

*/

WITH

XMLNAMESPACES(http://schemas.blah.com/Orders’ AS O)

SELECT TOP 5

SalesOrderID

AS "O:SalesOrderID",

RevisionNumber

AS "O:RevisionNumber",

OrderDate AS "O:OrderDate",

DueDate

AS "O:DueDate",

ShipDate

AS "O:ShipDate",

Status AS "O:Status",

OnlineOrderFlag

AS "O:OnlineOrderFlag",

SalesOrderNumber

AS "O:SalesOrderNumber",

PurchaseOrderNumber

AS "O:PurchaseOrderNumber",

AccountNumber AS "O:AccountNumber",

CustomerID

AS "O:CustomerID",

ContactID

AS "O:ContactID",

SalesPersonID AS "O:SalesPersonID",

TerritoryID

AS "O:TerritoryID",

BillToAddressID

AS "O:BillToAddressID",

ShipToAddressID

AS "O:ShipToAddressID"

FROM Sales.SalesOrderHeader

FOR XML PATH (‘O:Order’), ROOT(‘O:Orders’)

/*

When namespace is specified, how to save the xml document to a variable? The following code will not work. It gives the following error:

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword ‘WITH’.

Msg 319, Level 15, State 1, Line 3

Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Msg 102, Level 15, State 1, Line 22

Incorrect syntax near ‘)’.

*/

DECLARE @Orders xml;

SET

@Xml=(

WITH

XMLNAMESPACES(http://schemas.blah.com/Orders’ AS O)

SELECT

TOP 5

SalesOrderID

AS "O:SalesOrderID",

RevisionNumber AS "O:RevisionNumber",

OrderDate AS "O:OrderDate",

DueDate AS "O:DueDate",

ShipDate AS "O:ShipDate",

Status AS "O:Status",

OnlineOrderFlag

AS "O:OnlineOrderFlag",

SalesOrderNumber

AS "O:SalesOrderNumber",

PurchaseOrderNumber

AS "O:PurchaseOrderNumber",

AccountNumber

AS "O:AccountNumber",

CustomerID

AS "O:CustomerID",

ContactID

AS "O:ContactID",

SalesPersonID

AS "O:SalesPersonID",

TerritoryID AS "O:TerritoryID",

BillToAddressID

AS "O:BillToAddressID",

ShipToAddressID

AS "O:ShipToAddressID"

FROM

Sales.SalesOrderHeader

FOR

XML PATH (‘O:Order’), ROOT(‘O:Orders’), TYPE)

/*

To save the generated xml, we must use SELECT to assign the document to the variable and need to specify the name space before the SELECT statement.

The following code works fine:

*/

DECLARE

@Orders xml;

WITH

XMLNAMESPACES(http://schemas.blah.com/Orders’ AS O)

SELECT

@Orders=(

SELECT

TOP 5

SalesOrderID AS "O:SalesOrderID",

RevisionNumber

AS "O:RevisionNumber",

OrderDate

AS "O:OrderDate",

DueDate

AS "O:DueDate",

ShipDate

AS "O:ShipDate",

Status AS "O:Status",

OnlineOrderFlag

AS "O:OnlineOrderFlag",

SalesOrderNumber

AS "O:SalesOrderNumber",

PurchaseOrderNumber

AS "O:PurchaseOrderNumber",

AccountNumber AS "O:AccountNumber",

CustomerID

AS "O:CustomerID",

ContactID AS "O:ContactID",

SalesPersonID

AS "O:SalesPersonID",

TerritoryID

AS "O:TerritoryID",

BillToAddressID

AS "O:BillToAddressID",

ShipToAddressID

AS "O:ShipToAddressID"

FROM Sales.SalesOrderHeader

FOR XML PATH (‘O:Order’), ROOT(‘O:Orders’), TYPE)

SELECT

@Orders

/*

The generated xml document is as follow:

<O:Orders xmlns:O="http://schemas.blah.com/Orders"&gt;

<O:Order>

<O:SalesOrderID>43659</O:SalesOrderID>

<O:RevisionNumber>1</O:RevisionNumber>

<O:OrderDate>2001-07-01T00:00:00</O:OrderDate>

<O:DueDate>2001-07-13T00:00:00</O:DueDate>

<O:ShipDate>2001-07-08T00:00:00</O:ShipDate>

<O:Status>5</O:Status>

<O:OnlineOrderFlag>0</O:OnlineOrderFlag>

<O:SalesOrderNumber>SO43659</O:SalesOrderNumber>

<O:PurchaseOrderNumber>PO522145787</O:PurchaseOrderNumber>

<O:AccountNumber>10-4020-000676</O:AccountNumber>

<O:CustomerID>676</O:CustomerID>

<O:ContactID>378</O:ContactID>

<O:SalesPersonID>279</O:SalesPersonID>

<O:TerritoryID>5</O:TerritoryID>

<O:BillToAddressID>985</O:BillToAddressID>

<O:ShipToAddressID>985</O:ShipToAddressID>

</O:Order>

<O:Order>

<O:SalesOrderID>43660</O:SalesOrderID>

<O:RevisionNumber>1</O:RevisionNumber>

<O:OrderDate>2001-07-01T00:00:00</O:OrderDate>

<O:DueDate>2001-07-13T00:00:00</O:DueDate>

<O:ShipDate>2001-07-08T00:00:00</O:ShipDate>

<O:Status>5</O:Status>

<O:OnlineOrderFlag>0</O:OnlineOrderFlag>

<O:SalesOrderNumber>SO43660</O:SalesOrderNumber>

<O:PurchaseOrderNumber>PO18850127500</O:PurchaseOrderNumber>

<O:AccountNumber>10-4020-000117</O:AccountNumber>

<O:CustomerID>117</O:CustomerID>

<O:ContactID>216</O:ContactID>

<O:SalesPersonID>279</O:SalesPersonID>

<O:TerritoryID>5</O:TerritoryID>

<O:BillToAddressID>921</O:BillToAddressID>

<O:ShipToAddressID>921</O:ShipToAddressID>

</O:Order>

<O:Order>

<O:SalesOrderID>43661</O:SalesOrderID>

<O:RevisionNumber>1</O:RevisionNumber>

<O:OrderDate>2001-07-01T00:00:00</O:OrderDate>

<O:DueDate>2001-07-13T00:00:00</O:DueDate>

<O:ShipDate>2001-07-08T00:00:00</O:ShipDate>

<O:Status>5</O:Status>

<O:OnlineOrderFlag>0</O:OnlineOrderFlag>

<O:SalesOrderNumber>SO43661</O:SalesOrderNumber>

<O:PurchaseOrderNumber>PO18473189620</O:PurchaseOrderNumber>

<O:AccountNumber>10-4020-000442</O:AccountNumber>

<O:CustomerID>442</O:CustomerID>

<O:ContactID>281</O:ContactID>

<O:SalesPersonID>282</O:SalesPersonID>

<O:TerritoryID>6</O:TerritoryID>

<O:BillToAddressID>517</O:BillToAddressID>

<O:ShipToAddressID>517</O:ShipToAddressID>

</O:Order>

<O:Order>

<O:SalesOrderID>43662</O:SalesOrderID>

<O:RevisionNumber>1</O:RevisionNumber>

<O:OrderDate>2001-07-01T00:00:00</O:OrderDate>

<O:DueDate>2001-07-13T00:00:00</O:DueDate>

<O:ShipDate>2001-07-08T00:00:00</O:ShipDate>

<O:Status>5</O:Status>

<O:OnlineOrderFlag>0</O:OnlineOrderFlag>

<O:SalesOrderNumber>SO43662</O:SalesOrderNumber>

<O:PurchaseOrderNumber>PO18444174044</O:PurchaseOrderNumber>

<O:AccountNumber>10-4020-000227</O:AccountNumber>

<O:CustomerID>227</O:CustomerID>

<O:ContactID>564</O:ContactID>

<O:SalesPersonID>282</O:SalesPersonID>

<O:TerritoryID>6</O:TerritoryID>

<O:BillToAddressID>482</O:BillToAddressID>

<O:ShipToAddressID>482</O:ShipToAddressID>

</O:Order>

<O:Order>

<O:SalesOrderID>43663</O:SalesOrderID>

<O:RevisionNumber>1</O:RevisionNumber>

<O:OrderDate>2001-07-01T00:00:00</O:OrderDate>

<O:DueDate>2001-07-13T00:00:00</O:DueDate>

<O:ShipDate>2001-07-08T00:00:00</O:ShipDate>

<O:Status>5</O:Status>

<O:OnlineOrderFlag>0</O:OnlineOrderFlag>

<O:SalesOrderNumber>SO43663</O:SalesOrderNumber>

<O:PurchaseOrderNumber>PO18009186470</O:PurchaseOrderNumber>

<O:AccountNumber>10-4020-000510</O:AccountNumber>

<O:CustomerID>510</O:CustomerID>

<O:ContactID>97</O:ContactID>

<O:SalesPersonID>276</O:SalesPersonID>

<O:TerritoryID>4</O:TerritoryID>

<O:BillToAddressID>1073</O:BillToAddressID>

<O:ShipToAddressID>1073</O:ShipToAddressID>

</O:Order>

</O:Orders>

*/

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

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