Read all values from an xml node

• kumar-378458

  SSChasing Mays

  Points: 636

  More actions

  November 21, 2013 at 3:04 pm

  #395876

  Hi All,

  I have the following code and trouble reading values of Bank Accounts. If i remove the line it says "xmlns="http://applications.apch1.com/webservice/schema/" then i my query is working. But i cant remove this becasue that is what i will get response from a web service. All the records are stored in the database with this line included. Please help. Also please specify if there is any easy way to read these values.

  DECLARE @MyXML XML

  SET @MyXML = '<GetEmployeeDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <return xmlns="http://applications.apch1.com/webservice/schema/">

  <CustomerID> A8339 </CustomerID>

  <BankAccounts>

  <BankAccount>123456</BankAccount>

  <BankAccount>7890123</BankAccount>

  </BankAccounts>

  </return>

  </GetEmployeeDetails>'

  SELECT

  pref.value('./text()[1]', 'varchar(MAX)') AS 'BankAccount'

  FROM @MyXML.nodes('//GetEmployeeDetails/return/BankAccounts/child::node()') AS RequestXml(pref)

  where pref.value('local-name(.)[1]', 'varchar(100)') ='BankAccount'

  Thanks,

  Kum

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 21, 2013 at 6:09 pm

  #1668275

  You could ignore the namespace by using a pseudo-namespace and a * to name all namespaces:

  ;

  WITH XMLNAMESPACES(DEFAULT 'some_string')

  SELECT

  pref.value('./text()[1]', 'varchar(MAX)') AS 'BankAccount'

  FROM @MyXML.nodes('//*:GetEmployeeDetails/*:return/*:BankAccounts/child::node()') AS RequestXml(pref)

  where pref.value('local-name(.)[1]', 'varchar(100)') ='BankAccount'

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• kumar-378458

  SSChasing Mays

  Points: 636

  More actions

  November 21, 2013 at 8:05 pm

  #1668285

  Thank you LutzM.

  Can you also tell me how to get the CustomerID in the resultset ?

  SO the output should looks like:

  A8339 123456

  A8339 7890123

  Thank you all.