0

I am trying to save data values into a table so that I can use them but can't get my head around it. I am currently running SQL Server 2012. How do I read soap XML which is saved in @x variable?

declare @x xml

set @x = '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <ListResponse xmlns="http://api.com">
      <ListResult>
        <API>
          <ID>1565</ID>
          <Email>jd@dj.com</Email>
          <Type>Unknown</Type>
          <Notes />
        </API>
      </ListResult>
    </ListResponse>
  </soap:Body>
</soap:Envelope>'

This is what I come up but it is only returning data in a single column with no way of knowing which data belongs to which column

select convert(varchar, n.c.value('.', 'varchar(max)'))
from @X.nodes('//text()[1]') n(c)

1565
jd@dj.com
Unknown

Data is coming in column format where as i wants in row with each API returns. currently only 1 API return what if more then 1 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Declan Junior
  • 83
  • 7
  • 1
    I cant get my head around what you aim for. Someone providing a solution to store soap messages into your DB? a DB Designer designing your tables for you and writing some poco's or building an orm wrapper? I do not even see a programming language per se - so maybe take a few minutes to go over this question and specify what exactly your problem is. SO is not providing complete framework solutions to anxybodys ideas - its merely helping you code and fix errors if you are unable to solve them yourself after trying to & doing some research on the topic. – Patrick Artner Dec 25 '17 at 17:27
  • 1
    Maybe you should start with adding a TAG that describes what your _programming language_ is - so you target the correct ppl here. Ive used C#, Java, Python and Sybase, MySql, SqlServer 2008r2 onwards to store stuff into - If I do not understand what you want to achieve - chances are - others wont as well. So maybe _you_ should think about providing enough information as part of your questions so _we_ are able to help. Just a friendly hint.Ah forgot to add c++ , alhough not sure if I did DB with that ... – Patrick Artner Dec 25 '17 at 17:37
  • I am not sure what part of the question you don't understand. you need to read the subject as well, not just the description. All i requested that Can i read soap xml in sql and save values into table. Table has columns ID,Email,Type and Notes. i am looking for help not arguing with you, if you don't understand the question, just move on. – Declan Junior Dec 25 '17 at 17:41
  • We are getting there - Maybe change your questions title to `How to map/insert a soap request answer's properties (xml string) to an existing table with similar coulmn names` and provide the details about your target table inside your question. You mentioned SqlServer (but not used the TAG for it - which would SqlServer experts make look up and see your question) - so this might help you: https://stackoverflow.com/questions/38905120/how-to-parse-soap-xml-in-sql-server-and-show-as-table - have fun, I am done lecturing for today. – Patrick Artner Dec 25 '17 at 17:59
  • 1
    Ok, found the answer by myself. ;with xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as [soap], – Declan Junior Dec 25 '17 at 19:40

0 Answers0