XML issue

• Rob

  SSCrazy

  Points: 2178

  More actions

  December 5, 2014 at 9:00 am

  #293141

  Can anybody please help me with the XML below. I need to extract Initials,Gender and Phone info:

  DECLARE @XmlTable TABLE (ID INT NOT NULL, XMLDATA XML)

  INSERT INTO @XmlTable VALUES(1,

  '<updateProfilesRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <updateProfile xmlns="http://www.fictivewebsite.nl/ops/updateProfiles/v1.0">

  <fullRelation>

  <initials>J. M. C.</initials>

  <lastName>Fictive_last_name</lastName>

  <gender>Female</gender>

  <phoneNumber>

  <areaCode>0184</areaCode>

  <number>617896</number>

  </phoneNumber>

  </fullRelation>

  </updateProfile>

  </updateProfilesRequest>')

  ;WITH XMLNAMESPACES('http://www.fictivewebsite.nl/ops/updateProfiles/v1.0' AS ns1)

  SELECT

  Initials = XmlData.value('(updateProfilesRequest/updateProfile/ns1:fullRelation/ns1:initials)[1]', 'varchar(25)'),

  Gender = XmlData.value('(updateProfilesRequest/updateProfile/ns1:fullRelation/ns1:gender)[1]', 'varchar(25)'),

  PhoneArea = XmlData.value('(updateProfilesRequest/updateProfile/ns1:fullRelation/ns1:phonenumber/ns1:areacode)[1]', 'varchar(25)'),

  PhoneNo = XmlData.value('(updateProfilesRequest/updateProfile/ns1:fullRelation/ns1:phonenumber/ns1:number)[1]', 'varchar(25)')

  FROM

  @XmlTable

• g.britton

  SSChampion

  Points: 13863

  More actions

  December 5, 2014 at 10:14 am

  #1763023

  This works:

  DECLARE @XmlTable TABLE (ID INT NOT NULL, XMLDATA XML)

  INSERT INTO @XmlTable VALUES(1,

  '<updateProfilesRequest xmlns="http://www.fictivewebsite.nl/ops/updateProfiles/v1.0">

  <updateProfile >

  <fullRelation>

  <initials>J. M. C.</initials>

  <lastName>Fictive_last_name</lastName>

  <gender>Female</gender>

  <phoneNumber>

  <areaCode>0184</areaCode>

  <number>617896</number>

  </phoneNumber>

  </fullRelation>

  </updateProfile>

  </updateProfilesRequest>');

  WITH XMLNAMESPACES(DEFAULT 'http://www.fictivewebsite.nl/ops/updateProfiles/v1.0')

  SELECT

  Initials = XmlData.value('(/updateProfilesRequest/updateProfile/fullRelation/initials)[1]', 'varchar(25)'),

  Gender = XmlData.value('(/updateProfilesRequest/updateProfile/fullRelation/gender)[1]', 'varchar(25)'),

  PhoneArea = XmlData.value('(/updateProfilesRequest/updateProfile/fullRelation/phoneNumber/areaCode)[1]', 'varchar(25)'),

  PhoneNo = XmlData.value('(/updateProfilesRequest/updateProfile/fullRelation/phoneNumber/number)[1]', 'varchar(25)')

  FROM @XmlTable X

  Gerald Britton, Pluralsight courses

• Rob

  SSCrazy

  Points: 2178

  More actions

  December 5, 2014 at 10:31 am

  #1763031

  Many thanks !