Extract XML text from SQL Server

Question

I need to extract values from XML in SQL Server. However if I try this query:

select 
    xmldata.query('(/entry/accounts/account/accountid/text())')
from 
    ltr_CPC_phx_acct_balance_XML

Short example of my XML:

<entry timestamp="2015-08-05T10:25:54">
    <accounts>
    <account><accountid>xxxx0800USD</accountid><balance>-100,02</balance></account>
    <account><accountid>xxxx2100EUR</accountid><balance>215,36</balance></account>
    <account><accountid>xxxx4301GBP</accountid><balance>96,54</balance></account>
    </accounts>
</entry>

Result:

1. xxxx0800USDxxxx2100EURxxxx4301GBP

Expected result should by something like this

1. xxxx0800USD
2. xxxx2100EUR
3. xxxx4301GBP

Thank you very much.

har07 · Accepted Answer · 2015-08-12 14:26:12Z

2

You need to shred the XML by using nodes(), something like :

select 
    accountid.value('.','varchar(100)') as accountid
from ltr_CPC_phx_acct_balance_XML
    OUTER APPLY xmldata.nodes('/entry/accounts/account/accountid') T(accountid)

answered Aug 12, 2015 at 14:26

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract XML text from SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related