November 21, 2013 at 3:04 pm
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
November 21, 2013 at 6:09 pm
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'
November 21, 2013 at 8:05 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply