Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Read all values from an xml node Expand / Collapse
Author
Message
Posted Thursday, November 21, 2013 3:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 6, 2013 2:20 PM
Points: 14, Visits: 72
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
Post #1516598
Posted Thursday, November 21, 2013 6:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 6,880, Visits: 13,466
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1516615
Posted Thursday, November 21, 2013 8:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 6, 2013 2:20 PM
Points: 14, Visits: 72
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.
Post #1516628
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse