XQuery xml.value help

• G_Dubyah

  Mr or Mrs. 500

  Points: 584

  More actions

  September 27, 2011 at 4:41 am

  #258009

  Hi guys, I'm trying to query the following xml to get the value for the Answer of Question type "RoomName" i.e.HomeRoom but I can't get the code right. Any help would be appreciated.

  Thanks

  G

  This is what I have tried with no luck:

  WITH XMLNAMESPACES(DEFAULT 'http://www.doc.co.uk/1.0')

  select

  [Xml].value('(/College/WIT/CustomQuestions[Questions/@group="PrintData"]/Question[Question/@type="RoomName"]/Answer)[1]','nvarchar(30)')as RoomName

  from [db].[CollegeDetails]

  XML:

  <College>

  <WIT>

  <CustomQuestions>

  <Questions group="PrintData">

  <Question type="CourseCode">

  <Answer data="1001" />

  </Question>

  <Question type="RoomName">

  <Answer data="HomeRoom" />

  </Question>

  </Questions>

  </CustomQuestions>

  </WIT>

  </College>

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  September 27, 2011 at 5:49 am

  #1387050

  There are two problems I can see...

  Firstly, the XPATH is not quite right, and secondly, the namespace is not required.

  If you could provide sample data in an easily consumed format it makes it much easier:

  IF OBJECT_ID('tempdb..#CollegeDetails') IS NOT NULL

  DROP TABLE #CollegeDetails;

  CREATE TABLE #CollegeDetails(id INT IDENTITY,[XML] XML);

  INSERT

  #CollegeDetails

  VALUES

  ('<College>

  <WIT>

  <CustomQuestions>

  <Questions group="PrintData">

  <Question type="CourseCode">

  <Answer data="1001" />

  </Question>

  <Question type="RoomName">

  <Answer data="HomeRoom" />

  </Question>

  </Questions>

  </CustomQuestions>

  </WIT>

  </College>')

  And here is the working query:

  SELECT

  [XML].value('(/College/WIT/CustomQuestions/Questions[@group="PrintData"]/Question[@type="RoomName"]/Answer/@data)[1]', 'nvarchar(30)') AS RoomName

  FROM

  #CollegeDetails

  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]