Query XML

• Ken McKelvey

  SSCoach

  Points: 18890

  More actions

  February 8, 2021 at 12:11 pm

  Go to Answer

  #3842432

  I have not used XML for some time but now need to query against a xml format I have not used before. The best I seem to be able to get is blank columns:

  DECLARE @xml xml = 
  '<Users>
  <User id="user1">
  <add key="name" value="user1name" />
  <add key="dept" value="user1Dept" />
  <add key="email" value="user1@somewhere.com" />
  <add key="tel" value="1234" />
  </User>
  <User id="user2">
  <add key="name" value="user2name" />
  <add key="dept" value="user2Dept" />
  <add key="email" value="user2@somewhere.com" />
  </User>
  <User id="user3">
  <add key="name" value="user3name" />
  <add key="dept" value="user3Dept" />
  <add key="email" value="user3@somewhere.com" />
  <add key="mobile" value="5678" />
  </User>
  </Users>';
  
  SELECT N.usernode.query('id').value('.', 'varchar(10)') AS id
  ,N.usernode.query('email').value('.', 'varchar(10)') AS email
  FROM (values (@xml)) X(xmlcol)
  CROSS APPLY X.xmlcol.nodes(N'Users/User') AS N (usernode);

  Any ideas?

• Mark Cowne

  One Orange Chip

  Points: 26978

  More actions

  February 8, 2021 at 1:12 pm

  Answer

  #3842448

  See if this works

   

  SELECT N.usernode.value('@id','varchar(10)') AS id
  ,N.usernode.value('(add[@key="email"])[1]/@value', 'varchar(30)') AS email
  FROM (values (@xml)) X(xmlcol)
  CROSS APPLY X.xmlcol.nodes(N'/Users/User') AS N (usernode);

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Ken McKelvey

  SSCoach

  Points: 18890

  More actions

  February 8, 2021 at 1:19 pm

  #3842450

  Thank you Mark. I was getting very confused.