SQL SERVER FOR XML CASE RESULTS

In this post, i just want to show how to use FOR XML in SQL Queries and output results. The reason because i always bit confused with the result like for which type which output so i thought to simplify the concept to keep remember.
Here, i used two tables Customers & Orders. Table properties are shown below,
for xml
Query Used, Replace the highlighted portion for other FOR XML queries, RAW, AUTO, PATH.
SELECT OrderID, Name, Country, Amount, OrderDate
FROM dbo.customers c INNER JOIN dbo.orders o
ON C.CustomerID=o.CustomerID
WHERE c.customerId =101
FOR XML RAW

Query & Result
FOR XML RAW
<row OrderID="1" Name="Ravi" Country="India" Amount="250000"OrderDate="2014-06-25T00:00:00" />

FOR XML RAW, ELEMENTS

<row>
  <OrderID>1OrderID>
  <Name>RaviName>
  <Country>IndiaCountry>
  <Amount>250000Amount>
  <OrderDate>2014-06-25T00:00:00OrderDate>
row>

FOR XML AUTO

<o OrderID="1" Amount="250000" OrderDate="2014-06-25T00:00:00">
  <c Name="Ravi" Country="India" />
o>

FOR XML AUTO, ELEMENTS

<o>
  <OrderID>1OrderID>
  <Amount>250000Amount>
  <OrderDate>2014-06-25T00:00:00OrderDate>
  <c>
    <Name>RaviName>
    <Country>IndiaCountry>
  c>
o>

FOR XML PATH

&

FOR XML PATH, ELEMENTS

<row>
  <OrderID>1OrderID>
  <Name>RaviName>
  <Country>IndiaCountry>
  <Amount>250000Amount>
  <OrderDate>2014-06-25T00:00:00OrderDate>
row>

FOR XML PATH ('customers')

<customers>
  <OrderID>1OrderID>
  <Name>RaviName>
  <Country>IndiaCountry>
  <Amount>250000Amount>
  <OrderDate>2014-06-25T00:00:00OrderDate>
customers>


Comments

Post a Comment

Popular posts from this blog

SQL Server–Alter failed for Server

Image
When you  get below error message, “Alter failed for server ‘’ The execute permission was denied on the object ‘xp_instance_regwrite’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error:229)” If you are login as SQL Server authentication mode and trying to change the server authentication mode in the server properties. Solution: How to avoid this error, if you want to change the server authentication mode to Windows authentication, first you have to login as windows authentication and then go to server properties and change the mode. Make sure always login as windows authentication mode to change the server authentication mode properties.
Read more

Generate Data Time Dimension Table in SAP HANA

Image
We can generate the Date Time dimension table in SAP HANA very easily by existing feature. In the Quick Launch option you can able to see “Generate Date Time Dime” Follow the options and create a table either fiscal or Gregorian. The created tables will be reside under _SYS_BI schema.If the you are chosen Gregorian option then the table name will be “"M_TIME_DIMENSION”. Table name may change based on the granularity that you selected. If you are choose week as granularity then table name will be “M_TIME_DIMENSION_WEEK”. If the option is Fiscal then table name will be “M_FISCAL_CALENDAR”
Read more

Access HANA view in SQL Console

Image
For each view, system will create a table whether it might be SAP HANA LIVE view or Calculation view or Attribute view. Those tables will be reside in system schema "_SYS_BIC" Expand the schema -> Column Views. There you can see lot of tables. Tables will be available for each view and each column inside that view. Make sure to get the main view. For example if you take HANA Live View "VBAK_COM". --Main View table select top 10 * from "_SYS_BIC"."sap.hba.ecc/VBAK_COM/" To select view with particular column, follow below select top 10 "SalesDocument" from "_SYS_BIC"."sap.hba.ecc/VBAK_COM" The above table can be access in SQL console, it will give the output which is same as what you have seen in the data preview. The other tables also available for columns select top 10 * from "_SYS_BIC"."sap.hba.ecc/VBAK_COM/SalesDocument/hier/SalesDocument" The above table will give deta...
Read more