Naveen's Weblog

Bridge to future

Posts Tagged ‘XML’

MSSQL Output in XML using For XML

Posted by codingsense on December 31, 2008


As we know nowadays XML is extensively used for any transfer between media so we should know how to get output of any user data in XML form. In MSSQL 2005 life has been made easier with For XML Keyword. In this section we will see how to get different types of XML output in MSSQL.

Open Management studio, and as i explain each keyword, execute the queries and check the output for better understanding.

First let us create 2 tables which will be refferred in the below section

Employee Table :

CREATE TABLE [dbo].[Employee](
	[ID] [int] NOT NULL Primary Key,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[DOB] [datetime] NULL)
GO

EmployeeSalary Table:

CREATE TABLE [dbo].[EmpSalary](
	[ID] int NULL,	
	[SalariedMonth] varchar(4) NULL
)

Insert Data in the tables:

Insert into Employee select 1,'First1','Last1','08/04/1983'
Insert into EmployeeSalary select 1,'Apr'
Insert into EmployeeSalary select 1,'May'
Insert into EmployeeSalary select 1,'Jun'

Insert into Employee select 2,'First2','Last2','08/04/1984'
Insert into EmployeeSalary select 2,'Apr'
Insert into EmployeeSalary select 2,'Jun'

Insert into Employee select 3,'First3','Last3',null
Insert into EmployeeSalary select 3,'May'
Insert into EmployeeSalary select 3,'Jun'

Raw: – Gives as the element name for each row

select FirstName,LastName from Employee for xml raw

Auto: – Gives Table Name as the element name for each Row

select FirstName,LastName from Employee for xml AUTO

Elements:
Without Elements keyword a row with all the columns will be converted in one XML node element.
With Elements Keyword a row will be converted into Parent Node and each column will be converted into a child node.

select FirstName,LastName from Employee for xml AUTO , elements
XSINIL: While selecting columns if the column value is null then it will not be displayed in the XML. If we want to display the column even it has Null Values, then we have to use XSINIL. By using this, if the column has values then the respective value will be displayed or xsi:nil="true" will be displayed for the NULL values.
select FirstName,LastName, DOB from Employee for xml path, ELEMENTS XSINIL

'@':
@ keyword has to be used in 'As Clause' of the column if we want the Column to be considered as an Tag attribute and display in the parent tag.

select ID as '@ID',FirstName,LastName from Employee for xml path, ELEMENTS 

Root:
Root creates a root node with the name specified in the parameter
For eg:
root('Client') create .... on both ends of the XML generated

select * from Employee for xml path, ELEMENTS XSINIL, root('Client')

Joining 2 Tables:
Now if you want both the tables to be joined and the output has to be shown in a single XML file then there are many methods i will show you the two output which i have come accross.
Consider that for each employee we need to show the months in which he has earned the salary, then within the Employee node we will insert one more salary paid node and display the months.

1) Here a salary Tag will be displayed under each Employee tag and will contain the list of months in separate child nodes in which the employee has been paid the salary.

select Employee.ID as '@ID', FirstName,LastName,DOB , 
	(select SalariedMonth as 'Month' from employeesalary where 
	employee.id = employeesalary.id for xml path(''),Type) as 'Salary' 
from Employee
for xml path('Employee'),Elements XSINIL,root('Employees')

2) Here we use data() method to merge all the child entries into one row in the salary tag. This will give only one element named 'salary' under employee tag.

select Employee.ID as '@ID', FirstName,LastName,DOB , 
	(select SalariedMonth as 'data()' from employeesalary where 
	employee.id = employeesalary.id for xml path(''),Type) as 'Salary' 
from Employee
for xml path('Employee'),Elements XSINIL,root('Employees')

As you see in above example there is no much difference in both the methods except the 'AS clause' in subquery, but the output is pretty different.

Data():-
If for each parent record if there exists multiple children then we can use data() to display the parent and all children together in a single tag.
The 2nd example in previous section can be used to know about the Data() method.

There are lot many things to know, with the above things you can just begin smoothly.

More Information:
http://technet.microsoft.com/en-us/library/ms345137(SQL.90).aspx#forxml2k5_topic8

Happy Learning 🙂

Advertisements

Posted in MSSQL | Tagged: , | Leave a Comment »

Processes to XML using LINQ

Posted by codingsense on September 26, 2008


We have seen what is LINQ to objects and LINQ to XML now lets do some work that would need the combination of both.

Let us create an XML file which will consist of top 4 processes that are consuming high memory.

var MyProcess = from Proc in Process.GetProcesses()
where Proc.PagedMemorySize64 > 10000000
orderby Proc.PagedMemorySize64 descending
select new
XElement(“Process”,
new XElement(“Name”, Proc.ProcessName),
new XElement(“Memory”, Proc.PagedMemorySize64)
);

Console.WriteLine(new XElement(“Processes”, MyProcess.Take(4)));


Just have a closer look at what is being done. In MyProcess variable we get the list of all the process in the system those are taking more than 10000000 bytes of memory in desc order. While printing in Console.WriteLine we filter the top 4 process using Take Method.

Keep Learning 🙂

Posted in LINQ to XML | Tagged: , , | Leave a Comment »

Grouping XML Elements using Group By

Posted by codingsense on September 24, 2008

Since you have seen how to create an XML and fetch simple data from it, lets move to next level. In this sample we shall Group the elements and create our own XML from an existing XML.

Download Sample – 26Kb

Lets us consider a clientInfo XML which will store client Name and his Address(City,Area), now consider a situaltion where we would require to list all the employes grouped with their area name, How it can be done?. Here it goes.

XElement GroupAreas = new XElement("Areas",
            from client in ClientInfo.Elements()
            group client by client.Element("Address").Element("Area").Value into GroupedNames
            select new XElement(GroupedNames.Key,
                   GroupedNames.Elements("Name")
            ));

After this the clients will be grouped on their area name.

OutPut:

Happy Learning 🙂

Posted in LINQ to XML | Tagged: , , | 4 Comments »

Join XML using LINQ

Posted by codingsense on September 23, 2008

Hi All,

Today i have come with joining of 2 XML files using a relation using linq.

I will take a scenario where the clients are recognized by clientID , the basic information of each client (ID, Name and address) is stored at one place and the salary details (ID, Salary) will be stored at another place. Lets see what can be done If we need to join both these files into one.

 

Problem View::

Download Sample – 29kb

Create ClientInfo XML::

Here we shall create an XML of client data. Consider ID to be Unique Number (primary key). For each client we will have details like ID, name and address.

XElement ClientInfo = new XElement("Clients",
new XElement("Client",
new XElement("ID", "1"),
new XElement("Name", "Client1"),
new XElement("Address", "Mahalaxmi")
),
new XElement("Client",
new XElement("ID", "2"),
new XElement("Name", "Client2"),
new XElement("Address", "J.C.Road")
),
new XElement("Client",
new XElement("ID", "3"),
new XElement("Name", "Client3"),
new XElement("Address", "M.G.Road")
)
);

 

Output::

Create Salary XML::

Now we shall create an XML that will store the salary of each clients with their respective client ID.

XElement Salary = new XElement("Salaries",
new XElement("Salary",
new XElement("ID", "1"),
new XElement("Amount", "20000")
),
new XElement("Salary",
new XElement("ID", "3"),
new XElement("Amount", "30000")
),
new XElement("Salary",
new XElement("ID", "3"),
new XElement("Amount", "40000")
)
);

Output::


Join Both XML::

Lets try to join both the XML using the client ID and make them into one XML that will consist some elements of client info and some of salary .

XElement Join = new XElement("Clients",
from client in ClientInfo.Elements()
from sal in Salary.Elements()
where client.Element("ID").Value == sal.Element("ID").Value
select new XElement("Client", client.Descendants(), sal.Elements("Amount"))
);

Output:

Hope all of you got how to join two XML using an element as relation using LINQ.

Keep Exploring 🙂

Posted in LINQ to XML | Tagged: , , | Leave a Comment »

LINQ to XML basic sample

Posted by codingsense on September 22, 2008

The Microsoft definition:

LINQ to XML provides an in-memory XML programming interface that leverages the .NET Language-Integrated Query (LINQ) Framework. LINQ to XML uses the latest .NET Framework language capabilities and is comparable to an updated, redesigned Document Object Model (DOM) XML programming interface.

The namespace for dealing with XML is System.Xml.Linq

Download Sample – 26.4Kb

Basic Sample of LINQ to XML:

In this sample we will concentrate on how a XML can be created and accessed.
We will create a XML which will hold the client Information’s (ID,Name,Address).

Create a XML element:

XElement ClientInfo = new XElement("Clients",
new XElement("Client",
new XElement("ID", "1"),
new XElement("Name", "Client1"),
new XElement("Address", "Mahalaxmi")
),
new XElement("Client",
new XElement("ID", "2"),
new XElement("Name", "Client2"),
new XElement("Address", "J.C.Road")
),
new XElement("Client",
new XElement("ID", "3"),
new XElement("Name", "Client3"),
new XElement("Address", "M.G.Road")
)
);

Here XElement is an element of XML. It can be a single element or a child of another element or a parent with many more elements within it.

Output:

Only Client ID’s in a string Array::

Now lets try to take only client ID’s in a string array. Here is the code

string[] OnlyIDArr = (from Client in ClientInfo.Elements()
                      select Client.Element("ID").Value).ToArray<string>();

Output:

Create XML with only Client ID’s::

Here we will try to create an XML of only client ID’s

var OnlyID = from Client in ClientInfo.Elements()
             select new XElement("Client", Client.Element("ID"));

Output:

Nowadays XML is playing a vital role in many softwares and LINQ can be utilised to make it much easy, so try to explore it.

Keep Learning 🙂

Posted in LINQ to XML | Tagged: , , | 1 Comment »