For XML PATH ROOT in SQL Server 2008

Now and Then I used to observe that when it comes to XML, .NET and SQL Developer start fearing. This is not because of It is VERY DIFFICULT but because of unawareness. Today I am going to show you the use of  “FOR XML PATH”  clause in SQL Server.
Suppose we have ID, FirstName and LastName columns in one of our SQL Server table and we want output something like this:
<Employees>
  <Employee>
    <ID value=1 />
    <FirstName value=Ritesh />
    <LastName value=Shah />
  Employee>
  <Employee>
    <ID value=2 />
    <FirstName value=Rajan />
    <LastName value=Jain />
  Employee>
Employees>
Let me give you TSQL to generate table in SQL Server and insert records in that.
Create Table EmployeeData
(
      ID INT Identity(1,1),
      FirstName varchar(10),
      LastName varchar(10)
)
insert into EmployeeData
select ‘Ritesh’,‘Shah’ UNION ALL
select ‘Rajan’,‘Jain’
GO
So now, here is the use of “FOR XML PATH ROOT” clause which will make our life easier in this kind of situation.
select ID as “ID/@value”
     , FirstName as “FirstName/@value”
     , LastName as “LastName/@value”
from (
   Select * from EmployeeData
) as t
for xml path(‘Employee’), root(‘Employees’);
Isn’t it easy to use?
BTW, below given are some links which will redirect you to my some of the past articles on XML subject.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

For XML PATH ROOT in SQL Server 2008

Now and Then I used to observe that when it comes to XML, .NET and SQL Developer start fearing. This is not because of It is VERY DIFFICULT but because of unawareness. Today I am going to show you the use of  “FOR XML PATH”  clause in SQL Server.
Suppose we have ID, FirstName and LastName columns in one of our SQL Server table and we want output something like this:
<Employees>
  <Employee>
    <ID value=1 />
    <FirstName value=Ritesh />
    <LastName value=Shah />
  Employee>
  <Employee>
    <ID value=2 />
    <FirstName value=Rajan />
    <LastName value=Jain />
  Employee>
Employees>
Let me give you TSQL to generate table in SQL Server and insert records in that.
Create Table EmployeeData
(
      ID INT Identity(1,1),
      FirstName varchar(10),
      LastName varchar(10)
)
insert into EmployeeData
select ‘Ritesh’,‘Shah’ UNION ALL
select ‘Rajan’,‘Jain’
GO
So now, here is the use of “FOR XML PATH ROOT” clause which will make our life easier in this kind of situation.
select ID as “ID/@value”
     , FirstName as “FirstName/@value”
     , LastName as “LastName/@value”
from (
   Select * from EmployeeData
) as t
for xml path(‘Employee’), root(‘Employees’);
Isn’t it easy to use?
BTW, below given are some links which will redirect you to my some of the past articles on XML subject.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Update UnTyped XML data column in SQL Server 2008/2005

Now a day, XML is booming, it is welcomed from every platform so why Microsoft and especially SQL Server sit back? After SQL Server 2005, Microsoft have provided so many functionality for make developer’s life easy who are using XML. Today, I would like you to show how you can update un-typed XML data from within well known UPDATE T-SQL statement.
–create table for testing purpose with XML column
–in SQL Server 2005 or in 2008
create table xmlTest
(
ColumnXML xml
)
GO
–insert data in XML column
insert into xmlTest
select

 <Orders>  
  <OrderID>A1000</OrderID>  
  <ClientName>Ritesh Shah</ClientName>  
  <Product>HP Notebook</Product>  
</Orders>
GO
–check the output
select * from xmlTest
GO
–update value (un-typed) in product tag
DECLARE @Val varchar(50) 
SELECT @Val = ‘Dell Inspiron’ 
 
UPDATE xmlTest 
SET ColumnXML.modify(‘replace value of (/Orders/Product/text())[1] with sql:variable(“@Val”)’) 
GO
–confirm changes
select * from xmlTest



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Read XML node on same level inSQL Server 2008/2005

I have written one article few days back for loading relational data in SQL Server from XML file. I got very good response from that article so I am tempted write another one on XML. Suppose you have different occurrences of data within same level of XML file, how would you query or populate in SQL Server? Let us have a look at it.
–create one variable with XML data
–you can have .XML file saved on your disk
–and can use the same query
DECLARE @xData xml;

SET @xData =
<chem>
   <employee>
    <id>ch001</id>
    <FName>Ritesh</FName>
    <LName>Shah</LName>
    <dept>MIS</dept>
    <dept>Network</dept>
    <dept>DBA</dept>
 </employee>
   <employee>
    <id>ch002</id>
    <FName>Rushik</FName>
    <LName>Shah</LName>
    <dept>Programmer</dept>
    <dept>Developer</dept>
 </employee>
</chem>’
–suppose employee work in more than one department
–how would you list employee with all concern department?
–ofcourse with below give query 😉
SELECT outr.ID,outr.FName, outrmost.y.value(‘text()[1]’,‘varchar(200)’) AS imageUrl
FROM
(
        SELECT
            Inr.i.value(‘id[1]’,‘varchar(20)’) AS ID,
            Inr.i.value(‘FName[1]’,‘varchar(20)’) + ‘ ‘ + Inr.i.value(‘LName[1]’,‘varchar(20)’) AS FName,
            Inr.i.query(‘.’) AS employee_desc
        FROM @xData.nodes(‘/chem/employee’) AS Inr(i)
) AS outr
CROSS APPLY outr.employee_desc.nodes(‘//dept’) AS outrmost(y)
–or you can just list of department based on ID
SELECT outrmost.y.value(‘text()[1]’,‘varchar(200)’) AS imageUrl
FROM
(
        SELECT
            Inr.i.value(‘id[1]’,‘varchar(20)’) AS ID,
            –Inr.i.value(‘FName[1]’,’varchar(20)’) + ‘ ‘ + Inr.i.value(‘LName[1]’,’varchar(20)’) AS FName,
            Inr.i.query(‘.’) AS employee_desc
        FROM @xData.nodes(‘/chem/employee’) AS Inr(i)
) AS outr
CROSS APPLY outr.employee_desc.nodes(‘//dept’) AS outrmost(y)
where outr.ID=‘ch001’
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Load Relational XML data in SQL Server 2005

When question comes to play with XML, even seasoned programmer try to get away from it, not because it is difficult task but lack of knowledge. I promise, loading XML data into SQL Server 2005 as a relational data, is not going to be as difficult as you are thinking.
Let me show you one example which will fill two relational tables in SQL Server from XML data. Let us first create one XML file at C:\vendor.XML with following data in it.
   <vendor>
<c>
  <venID>101</venID>
  <first_name>James</first_name>
  <last_name>Butler</last_name>
<po>
  <poid>1001</poid>
  <venID>101</venID>
  <po_date>2008-04-01T00:00:00</po_date>
  <price>400.00</price>
  </po>
<po>
  <poid>1002</poid>
  <venID>101</venID>
  <po_date>2008-05-01T00:00:00</po_date>
  <price>299.0000</price>
 </po>
<po>
  <poid>1002</poid>
  <venID>101</venID>
  <po_date>2009-04-01T00:00:00</po_date>
  <price>600.0000</price>
</po>
 </c>

</vendor>

<

Now, create two tables to feed vendor details and purchase order detail, than we will load XML file into both tables.
–create table to hold Vendor information
CREATE TABLE vendor (
        venid INT NOT NULL
        , first_name VARCHAR(50)
        , last_name VARCHAR(50)
        )
–create table for holding Purchase order information.
CREATE TABLE PurcharOrder (
        poID INT NOT NULL
        , venid INT NOT NULL
        , po_date DATETIME
        , price MONEY
        )
GO
–hold xml from file
DECLARE @x XML
SET @x= (
SELECT xm.Col1 FROM OPENROWSET(BULK ‘c:\vendor.xml’,SINGLE_BLOB) AS xm(Col1)
         )
— show file contents
select @x
INSERT INTO vendor
SELECT
ven.value(‘./venID[1]’, ‘INT’) as vid
, ven.value(‘./first_name[1]’, ‘VARCHAR(50)’) as first_name
, ven.value(‘./last_name[1]’, ‘VARCHAR(50)’) as last_name
–, C.query(‘.’) 
FROM @x.nodes(‘/vendor/c’) tab(ven)
INSERT INTO PurcharOrder
SELECT
ven.value(‘./poid[1]’, ‘INT’) as POID
, ven.value(‘./venID[1]’, ‘INT’) as VenID
, ven.value(‘./po_date[1]’, ‘DATETIME’) as po_date
, ven.value(‘./price[1]’, ‘MONEY’) as price
–,ven.query(‘.’)
FROM @x.nodes(‘/vendor/c/po’) tab(ven)
GO
–check both table, whether data comes or not.
select * FROM Vendor
Select * from PurcharOrder


Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com