Why dont i get the correct XML format while querying?

Question

Why dont i get the correct XML format while querying?

hurricaneDBA

SSCertifiable

Points: 5027
More actions
February 15, 2017 at 4:08 am

#335291

Dear Everyone
I am trying to format the XML data correctly in my SSMS but when i try to run the following query:
USE AdventureWorks2012;
GO

SELECT TOP 2
Sales.SalesOrderIDAS SalesID,
Territory.name AS TerritoryName,
Country.name AS CountryName
FROM Sales.SalesOrderHeaderSales
INNER JOIN Sales.SalesTerritoryTerritory
ONSales.TerritoryID =Territory.TerritoryID
INNER JOIN Person.CountryRegionCountry
ONTerritory.CountryRegionCode = Country.CountryRegionCode
FOR XML RAW;

i am supposed to get the following result in 2 lines
<rowSalesID="43664" TerritoryName="Northwest"CountryName="United States" />
<row SalesID="43665" TerritoryName="Northwest"CountryName="United States" />
Yes i get the result as 1 line
Im guessing its an option i have to enable or check but where do i do this?
kindly advise
thanks
Kal

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

hurricaneDBA - Wednesday, February 15, 2017 4:08 AM
Dear Everyone
I am trying to format the XML data correctly in my SSMS but when i try to run the following query:
USE AdventureWorks2012;
GO

SELECT TOP 2
Sales.SalesOrderIDAS SalesID,
Territory.name AS TerritoryName,
Country.name AS CountryName
FROM Sales.SalesOrderHeaderSales
INNER JOIN Sales.SalesTerritoryTerritory
ONSales.TerritoryID =Territory.TerritoryID
INNER JOIN Person.CountryRegionCountry
ONTerritory.CountryRegionCode = Country.CountryRegionCode
FOR XML RAW;

i am supposed to get the following result in 2 lines
<rowSalesID="43664" TerritoryName="Northwest"CountryName="United States" />
<row SalesID="43665" TerritoryName="Northwest"CountryName="United States" />
Yes i get the result as 1 line
Im guessing its an option i have to enable or check but where do i do this?
kindly advise
thanks
Kal

Sorry, but that's not an option that I know of. You have the two records in XML format, but entirely within one output row of a query. If you want to then further process that XML, you can query it. It would be impractical to query it if it were separated into two distinct rows.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Thom A SSC Guru Points: 99133 More actions · Answer 2

You'd need to do it as a subquery:
SELECT TOP 2 CAST((SELECT Sales.SalesOrderID AS SalesID, Territory.name AS TerritoryName, Country.name AS CountryName FROM Sales.SalesOrderHeader Salessq INNER JOIN Sales.SalesTerritory Territory ON Sales.TerritoryID =Territory.TerritoryID INNER JOIN Person.CountryRegion Country ON Territory.CountryRegionCode = Country.CountryRegionCode WHERE Salessq.SalesOrderID = Sales.SalesOrderID FOR XML RAW) AS xml) AS SalesOrderXML FROM Sales.SalesOrderHeader Sales

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

sgmunson - Wednesday, February 15, 2017 9:29 AM
Sorry, but that's not an option that I know of. You have the two records in XML format, but entirely within one output row of a query. If you want to then further process that XML, you can query it. It would be impractical to query it if it were separated into two distinct rows.

Don't think "rows"... think "lines" for this. It's for readability purposes by humans when the output is directed to a file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)