Select element value as column from Nested XML namespaces

• iBar

  SSCertifiable

  Points: 5872

  More actions

  July 8, 2011 at 4:17 am

  #389937

  HI

  i want to select item "coke" as a column. (xQuery, xPath etc....)

  I have tried this and its fine in header but due to nested xml namespaces i.e. "a:", i am not getting Coke in select but returing NULL.

  Please write a "select statement" from this example temp.

  CREATE TABLE #temp

  (

  order_id int identity(1,1),

  item_xml xml

  )

  INSERT INTO #temp (item_xml)

  VALUES

  ('<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">

  <s:Header>

  <a:Action s:mustUnderstand="1" u:Id="_2">http://tempuri.org/INewPolicyService/ExecuteNewPolicy</a:Action&gt;

  <a:MessageID u:Id="_3">urn:uuid:5e2c43d5-54dd-49a6-8390-750f62ede139</a:MessageID>

  <a:ReplyTo u:Id="_4">

  <a:To s:mustUnderstand="1" u:Id="_5">http://localhost:49458/NewPolicyService.svc</a:To&gt;

  <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address&gt;

  </a:ReplyTo>

  </s:Header>

  <s:Body u:Id="_0">

  <ExecuteNewPolicy xmlns="http://tempuri.org/">

  <NewFacts xmlns:a="http://schemas.datacontract.org/2004/07/New.Specialized.NewPolicyService.DataContracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

  <a:NewOrders>

  <a:NewOrder>

  <a:item>Coke</a:item>

  <a:place>Airport</a:place>

  </a:NewOrder>

  <a:NewOrder>

  <a:item>Pepsi</a:item>

  <a:place>Bus</a:place>

  </a:NewOrder>

  </a:NewOrders>

  <a:HasOptOutPermission>false</a:HasOptOutPermission>

  <a:Security>

  <a:Application>

  <a:ID>2</a:ID>

  <a:Name>New name</a:Name>

  </a:Application>

  </a:Security>

  <a:SelectedPolices />

  </NewFacts>

  <reselectPolicices>false</reselectPolicices>

  </ExecuteNewPolicy>

  </s:Body>

  </s:Envelope>')

  select * from #temp

  drop table #temp

  Thanks.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  July 8, 2011 at 4:52 am

  #1350239

  with xmlnamespaces ( default 'http://tempuri.org/',

  'http://www.w3.org/2003/05/soap-envelope' as s,

  'http://schemas.datacontract.org/2004/07/New.Specialized.NewPolicyService.DataContracts' as a)

  select r.value('.','varchar(10)')

  from #temp

  cross apply item_xml.nodes('/s:Envelope/s:Body/ExecuteNewPolicy/NewFacts/a:NewOrders/a:NewOrder/a:item') as x(r)

  ____________________________________________________

  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

• iBar

  SSCertifiable

  Points: 5872

  More actions

  July 8, 2011 at 7:21 am

  #1350342

  Great Now,

  The pupose of question was to select from multiple nested namespaces and with the same name of namespace.

  Now i also want to see as column value from <a:To ..... </a:To> from Header

  Please can you extend your query to there as well? so that a: namespace both in header and body will work.

  I have also edited the example a bit and added "Pepsi" as order item as well. Now its returuning 2 rows. can we actually get each item as column?

  Thanks.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  July 8, 2011 at 7:36 am

  #1350351

  This should work

  select b.value('.','varchar(10)') as Item,

  h.value('.','varchar(50)') as [To]

  from #temp

  outer apply item_xml.nodes('

  declare default element namespace "http://tempuri.org/";

  declare namespace s="http://www.w3.org/2003/05/soap-envelope";

  declare namespace a="http://schemas.datacontract.org/2004/07/New.Specialized.NewPolicyService.DataContracts";

  /s:Envelope/s:Body/ExecuteNewPolicy/NewFacts/a:NewOrders/a:NewOrder/a:item') as body(b)

  outer apply item_xml.nodes('

  declare namespace s="http://www.w3.org/2003/05/soap-envelope";

  declare namespace a="http://www.w3.org/2005/08/addressing";

  /s:Envelope/s:Header/a:ReplyTo/a:To') as header(h)

  ____________________________________________________

  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

• iBar

  SSCertifiable

  Points: 5872

  More actions

  July 8, 2011 at 8:54 am

  #1350391

  Excellent!!!

  This works with single element in body. Of course one cannot write n number of outer apply for each element in body. I have tested following and its giving ERROR.

  use tempdb

  go

  if object_id('tempdb.dbo.#temp') is not null drop table #temp

  CREATE TABLE #temp

  (

  order_id int identity(1,1),

  item_xml xml

  )

  INSERT INTO #temp (item_xml)

  VALUES

  ('<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">

  <s:Header>

  <a:Action s:mustUnderstand="1" u:Id="_2">http://tempuri.org/INewPolicyService/ExecuteNewPolicy</a:Action&gt;

  <a:MessageID u:Id="_3">urn:uuid:5e2c43d5-54dd-49a6-8390-750f62ede139</a:MessageID>

  <a:ReplyTo u:Id="_4">

  <a:To s:mustUnderstand="1" u:Id="_5">http://localhost:49458/NewPolicyService.svc</a:To&gt;

  <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address&gt;

  </a:ReplyTo>

  </s:Header>

  <s:Body u:Id="_0">

  <ExecuteNewPolicy xmlns="http://tempuri.org/">

  <NewFacts xmlns:a="http://schemas.datacontract.org/2004/07/New.Specialized.NewPolicyService.DataContracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

  <a:NewOrders>

  <a:NewOrder>

  <a:item>Coke</a:item>

  <a:place>Airport</a:place>

  </a:NewOrder>

  <a:NewOrder>

  <a:item>Pepsi</a:item>

  <a:place>Bus</a:place>

  </a:NewOrder>

  </a:NewOrders>

  <a:HasOptOutPermission>false</a:HasOptOutPermission>

  <a:Security>

  <a:Application>

  <a:ID>2</a:ID>

  <a:Name>New name</a:Name>

  </a:Application>

  </a:Security>

  <a:SelectedPolices />

  </NewFacts>

  <reselectPolicices>false</reselectPolicices>

  </ExecuteNewPolicy>

  </s:Body>

  </s:Envelope>')

  SELECT

  order_id

  ,item_xml

  ,h.value('.','varchar(50)') as [To]

  ,b.value('a:/item[1]','varchar(10)') as Item

  ,b.value('a:/place[1]','varchar(10)') as Item

  from #temp

  outer apply item_xml.nodes('

  declare default element namespace "http://tempuri.org/";

  declare namespace s="http://www.w3.org/2003/05/soap-envelope";

  declare namespace a="http://schemas.datacontract.org/2004/07/New.Specialized.NewPolicyService.DataContracts";

  /s:Envelope/s:Body/ExecuteNewPolicy/NewFacts/a:NewOrders/a:NewOrder') as body(b)

  outer apply item_xml.nodes('

  declare namespace s="http://www.w3.org/2003/05/soap-envelope";

  declare namespace a="http://www.w3.org/2005/08/addressing";

  /s:Envelope/s:Header/a:ReplyTo/a:To') as header(h)

  Thanks for your patience and great help.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  July 8, 2011 at 9:01 am

  #1350399

  MidBar (7/8/2011)

  ---

  Excellent!!!

  This works with single element in body. Of course one cannot write n number of outer apply for each element in body. I have tested following and its giving ERROR.

  use tempdb

  go

  if object_id('tempdb.dbo.#temp') is not null drop table #temp

  CREATE TABLE #temp

  (

  order_id int identity(1,1),

  item_xml xml

  )

  INSERT INTO #temp (item_xml)

  VALUES

  ('<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">

  <s:Header>

  <a:Action s:mustUnderstand="1" u:Id="_2">http://tempuri.org/INewPolicyService/ExecuteNewPolicy</a:Action&gt;

  <a:MessageID u:Id="_3">urn:uuid:5e2c43d5-54dd-49a6-8390-750f62ede139</a:MessageID>

  <a:ReplyTo u:Id="_4">

  <a:To s:mustUnderstand="1" u:Id="_5">http://localhost:49458/NewPolicyService.svc</a:To&gt;

  <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address&gt;

  </a:ReplyTo>

  </s:Header>

  <s:Body u:Id="_0">

  <ExecuteNewPolicy xmlns="http://tempuri.org/">

  <NewFacts xmlns:a="http://schemas.datacontract.org/2004/07/New.Specialized.NewPolicyService.DataContracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

  <a:NewOrders>

  <a:NewOrder>

  <a:item>Coke</a:item>

  <a:place>Airport</a:place>

  </a:NewOrder>

  <a:NewOrder>

  <a:item>Pepsi</a:item>

  <a:place>Bus</a:place>

  </a:NewOrder>

  </a:NewOrders>

  <a:HasOptOutPermission>false</a:HasOptOutPermission>

  <a:Security>

  <a:Application>

  <a:ID>2</a:ID>

  <a:Name>New name</a:Name>

  </a:Application>

  </a:Security>

  <a:SelectedPolices />

  </NewFacts>

  <reselectPolicices>false</reselectPolicices>

  </ExecuteNewPolicy>

  </s:Body>

  </s:Envelope>')

  SELECT

  order_id

  ,item_xml

  ,h.value('.','varchar(50)') as [To]

  ,b.value('a:/item[1]','varchar(10)') as Item

  ,b.value('a:/place[1]','varchar(10)') as Item

  from #temp

  outer apply item_xml.nodes('

  declare default element namespace "http://tempuri.org/";

  declare namespace s="http://www.w3.org/2003/05/soap-envelope";

  declare namespace a="http://schemas.datacontract.org/2004/07/New.Specialized.NewPolicyService.DataContracts";

  /s:Envelope/s:Body/ExecuteNewPolicy/NewFacts/a:NewOrders/a:NewOrder') as body(b)

  outer apply item_xml.nodes('

  declare namespace s="http://www.w3.org/2003/05/soap-envelope";

  declare namespace a="http://www.w3.org/2005/08/addressing";

  /s:Envelope/s:Header/a:ReplyTo/a:To') as header(h)

  Thanks for your patience and great help.

  I think you'll have to specify the namespace in each value.

  Change

  ,b.value('a:item[1]','varchar(10)') as Item

  ,b.value('a:place[1]','varchar(10)') as Item

  to

  ,b.value('declare namespace a="http://schemas.datacontract.org/2004/07/New.Specialized.NewPolicyService.DataContracts"; a:item[1]','varchar(10)') as Item

  ,b.value('declare namespace a="http://schemas.datacontract.org/2004/07/New.Specialized.NewPolicyService.DataContracts"; a:place[1]','varchar(10)') as Item

  Alternatively you can just do this

  ,b.value('*:item[1]','varchar(10)') as Item

  ,b.value('*:place[1]','varchar(10)') as Item

  ____________________________________________________

  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

• iBar

  SSCertifiable

  Points: 5872

  More actions

  July 10, 2011 at 12:56 am

  #1350797

  Great Champ!!!! Excellent work.

  BY giving the namespace in individual item, it worked as desired.

  However, I am still not sure what's the purpose of this "*." in this clause "b.value('*.item[1]','varchar(10)') as Item" and its not working at all in that way and giving error.

  Does this mean Anywhere?

  Thank you.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  July 11, 2011 at 1:56 am

  #1350915

  MidBar (7/10/2011)

  ---

  Great Champ!!!! Excellent work.

  BY giving the namespace in individual item, it worked as desired.

  However, I am still not sure what's the purpose of this "*." in this clause "b.value('*.item[1]','varchar(10)') as Item" and its not working at all in that way and giving error.

  Does this mean Anywhere?

  Thank you.

  '*' should match any namespace

  ____________________________________________________

  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