Extract proper data from a sql server XML column using XQuery

Question

I am having an issue trying to get proper data from an XML type column:

"<"ArrayOfAccountInformation xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.Balance.com/">
  "<"AccountInformation>
    "<"AccountNumber xmlns="https://Safouenmzah.com/">0100000000"<"/AccountNumber>
     "<"OutstandingAmount xmlns="https://Safouenmzah.com/">-909.55"<"/OutstandingAmount>
     "<"LastBilledAmount xmlns="https://Safouenmzah.com/" />
     "<"LastPaidDate xmlns="https://Safouenmzah.com/" />
    "<"Severance xmlns="https://Safouenmzah.com/" />
   "<"PaymentAmount xmlns="https://Safouenmzah.com/" />
    "<"DistributedAmount xmlns="https://Safouenmzah.com/">$"<"/DistributedAmount>
   "<"AccountInfo xmlns="https://Safouenmzah.com/">Safouen Mzah  - Residential"<"/AccountInfo>
 "<"/AccountInformation>
"<"/ArrayOfAccountInformation>

I am using this sql code:

;WITH XMLNAMESPACES ('http://www.Balance.com/' AS ns)
SELECT xmlResult.value('(/ns:ArrayOfAccountInformation)[1]','varchar(8000)') AS AcctInfo

FROM [dbo].[BalanceEnquiry_Transactions_Tracker]
WHERE BanlanceEnquiry_Transc_ID = 4
GO

This the result I am receiving always:
0100000000-909.55AEDSafouen Mzah  - Residential

This is the expected result:
0100000000

Can some one help on this please?

If you just want the account number should the query not be - xmlResult.value('(/ns:ArrayOfAccountInformation)[1]AccountNumber[1]', 'varchar(8000)') — GarethD
– GarethD, Commented May 19, 2014 at 12:03
I tried like this: -- extract (shred) values from XML column nodes using WITH XMLNAMESPACES ;WITH XMLNAMESPACES ('Balance' AS ns) SELECT xmlResult.value('(/ns:ArrayOfAccountInformation)[1]AccountNumber[1]','varchar(8000)') AS AcctInfo FROM [dbo].[BalanceEnquiry_Transactions_Tracker] WHERE BanlanceEnquiry_Transc_ID = 4 GO And I am getting this error message: — SafwanMZ
– SafwanMZ, Commented May 19, 2014 at 19:54

SafwanMZ · Accepted Answer · 2014-05-23 10:42:47Z

Finally I figured it out.

declare  @XML XML;


set @XML = '
"<"ArrayOfAccountInformation xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.Balance.com/">
"<"AccountInformation>
"<"AccountNumber xmlns="https://Safouenmzah.com/">0100000000"<"/AccountNumber>
 "<"OutstandingAmount xmlns="https://Safouenmzah.com/">-909.55"<"/OutstandingAmount>
 "<"LastBilledAmount xmlns="https://Safouenmzah.com/" />
 "<"LastPaidDate xmlns="https://Safouenmzah.com/" />
"<"Severance xmlns="https://Safouenmzah.com/" />
"<"PaymentAmount xmlns="https://Safouenmzah.com/" />
"<"DistributedAmount xmlns="https://Safouenmzah.com/">$"<"/DistributedAmount>
"<"AccountInfo xmlns="https://Safouenmzah.com/">Safouen Mzah  - Residential"    <"/AccountInfo>
"<"/AccountInformation>
"<"AccountInformation>
"<"AccountNumber xmlns="https://Safouenmzah.com/">0200000000"<"/AccountNumber>
 "<"OutstandingAmount xmlns="https://Safouenmzah.com/">-908.55"<"/OutstandingAmount>
 "<"LastBilledAmount xmlns="https://Safouenmzah.com/" />
 "<"LastPaidDate xmlns="https://Safouenmzah.com/" />
"<"Severance xmlns="https://Safouenmzah.com/" />
"<"PaymentAmount xmlns="https://Safouenmzah.com/" />
"<"DistributedAmount xmlns="https://Safouenmzah.com/">$"<"/DistributedAmount>
"<"AccountInfo xmlns="https://Safouenmzah.com/">Safouen Mzah  - Expat"<"/AccountInfo>
"<"/AccountInformation>
"<"/ArrayOfAccountInformation>
'

;WITH XMLNAMESPACES ('http://www.Balance.com/' AS ns)
-- To get the outstanding amount of the first Account from the returned array
SELECT @XML.value('(/*[1]/*[1]/*[2])', 'varchar(200)') As OutstandingAmount1
-- To get the outstanding amount of the second Account from the returned array
SELECT @XML.value('(/*[1]/*[2]/*[2])', 'varchar(200)') As OutstandingAmount2

OutstandingAmount1
-909.55
OutstandingAmount2
-908.55

Mikael Eriksson · Accepted Answer · 2014-05-23 11:33:02Z

0

You can use nodes() to shred the XML and get the values in one resultset.

with xmlnamespaces('https://Safouenmzah.com/' as ns,
                   default 'http://www.Balance.com/')
select T.X.value('(ns:OutstandingAmount/text())[1]', 'varchar(200)')
from @XML.nodes('/ArrayOfAccountInformation/AccountInformation') as T(X)

SQL Fiddle

answered May 23, 2014 at 11:33

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

1 Comment

SafwanMZ Over a year ago

Thanks Mikael Eriksson !

Collectives™ on Stack Overflow

Extract proper data from a sql server XML column using XQuery

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related