Xml – sql 2005

DECLARE @XMLString XML

SET @XMLString =’

<Customers>

  <Customer>

    <FirstName>Kevin</FirstName>

    <LastName>Goff</LastName>

    <City>Camp Hill</City>
 <Schools>
  <HighSchool>Don Bosco1</HighSchool>
  <UG>ST1</UG>
  <PG>NSS1</PG>
 </Schools>

  </Customer>

  <Customer>

   <FirstName>Steve</FirstName>

    <LastName>Goff</LastName>

    <City> Philadelphia </City>
 <Schools>
  <HighSchool>Don Bosco2</HighSchool>
  <UG>ST2</UG>
  <PG>NSS2</PG>
 </Schools>

  </Customer>

</Customers>’

SELECT

  tab.col.value(‘FirstName[1]‘,’VARCHAR(20)’)AS FirstName,

  tab.col.value(‘LastName[1]‘,’VARCHAR(20)’)AS LastName,

  tab.col.value(‘City[1]‘,’VARCHAR(20)’)AS City,

  tab.col.value(‘Schools[1]/UG[1]‘,’VARCHAR(30)’) AS UG

FROM @XMLString.nodes(‘//Customer’) tab(col)

 

====================================================

 

 

 

 

 

 

 

 

 

select

 

 

UserDetails

 

.UserName ,UserDetails.AddressLine1,UserDetails.AddressLine2,UserDetails.UserID, State,

State from

UserDetails

 

join State on UserDetails.StateID=State. StateID for xml path (‘Record’), ROOT(‘Records’)

====================================================

 declare @T table ( id bigint)
 insert   into w_sponsor(userid,SponsorLogo)
 OUTPUT Inserted.sponsorid into  @T  values (1,’1′)
 
=======================

 declare @T table ( id bigint)
 update w_sponsor set userid=1
 OUTPUT Inserted.sponsorid into  @T  where sponsorid >10
 
select * from  @T
=======================
WITH CTE_Orders AS

(

SELECT Orderid, Shipname, shippeddate ,ROW_NUMBER() OVER (ORDER BY shippeddate DESC) AS RowNumber

FROM orders

)

SELECT Orderid, Shipname, shippeddate FROM CTE_Orders WHERE RowNumber BETWEEN 1 AND 30  ORDER BY RowNumber ASC;
=====================
Here 2 simple methods are explained which can accomplish paging at the Database level.

Method 2
———–

create procedure Paging_Sp2
@RecsPerPage int,
@Page int
as
begin
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Productid) AS Rownum, * FROM Products) AS Products_T
WHERE Rownum >= @RecsPerPage*(@Page-1) AND Rownum <= @RecsPerPage*(@Page)
end