I am facing a problem to fetch the attributes which are available in 'Day' Tag along with all other details. I have to fetch all the details at a time. Can you please provide the solution .. please find the below code

• mahesh.shiv

  SSC Enthusiast

  Points: 159

  More actions

  February 7, 2013 at 6:30 am

  #270502

  DECLARE

  @TrainingSessions XML

  SELECT

  @TrainingSessions = '<ProgramSessions>

  <Session>

  <Day IsHoliday="true" Reason="sdf">7</Day>

  <ActivityID>123</ActivityID>

  <ActivityDescription>Structured Queries</ActivityDescription>

  <StartDate>2012-03-1</StartDate>

  <StartTime>09:00:000</StartTime>

  <EndDate>2012-03-5</EndDate>

  <EndTime>17:00:000</EndTime>

  <TrainerID>123~3000,234~5000</TrainerID>

  <HallID>123454</HallID>

  </Session>

  </ProgramSessions>'

  DECLARE @Idoc INT,@I INT

  EXEC SP_XML_PREPAREDOCUMENT @Idoc OUTPUT,@TrainingSessions

  SELECT ActivityID,ActivityDescription,StartDate,StartTime,EndDate,EndTime,TrainerID,HallID,[Day],IsHoliday,Reason

  FROM OPENXML (@Idoc,'/ProgramSessions/Session',2)

  WITH ([Day] nvarchar(max),

  ActivityID BIGINT,

  ActivityDescription Nvarchar(500),

  StartDate DATETIME,

  StartTime Nvarchar(10),

  EndDate DATETIME,

  EndTime Nvarchar(10),

  TrainerID Nvarchar(100),

  HallID BIGINT,

  Reason Nvarchar(50),

  IsHoliday Nvarchar(50))

  SELECT IsHoliday,Reason

  FROM OPENXML(@Idoc,'/ProgramSessions/Session/Day',2)

  WITH (IsHoliday Nvarchar(50) '@IsHoliday',

  Reason Nvarchar(50) '@Reason')

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  February 7, 2013 at 9:45 am

  #1585537

  SELECT

  DayNumber = nd.value('(Day/text())[1]','smallint')

  , IsHoliday = nd.value('(Day/@IsHoliday)[1]','varchar(5)')

  , Reason = nd.value('(Day/@Reason)[1]','varchar(50)')

  , ActivityID = nd.value('(ActivityID/text())[1]','int')

  , ActivityDescription = nd.value('(ActivityDescription/text())[1]','varchar(512)')

  , StartDate = nd.value('(StartDate/text())[1]','date')

  , StartTime = nd.value('(StartTime/text())[1]','char(9)')

  , EndDate = nd.value('(EndDate/text())[1]','date')

  , EndTime = nd.value('(EndTime/text())[1]','char(9)')

  , TrainerID = nd.value('(TrainerID/text())[1]','varchar(100)')

  , HallID = nd.value('(HallID/text())[1]','varchar(100)')

  from @TrainingSessions.nodes('ProgramSessions/Session') as x(nd)

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]