Extract data from XML

Transact-SQL
,
1

HI all,

need your urgent help
i have a table changeevent and column changeddata contains XML file.
i need to extract the information from it.


true
2015-04-16T00:00:00+05:30
63747
63747
charucsrawat@hpcl
0
Gayatri Automobiles
Vandana Singh
16622710
0
false
Mirzapur
false
1
0
8001
NH7
Mirzapur Rewa Road
10000
200

0001-01-01T00:00:00
0
false

0001-01-01T00:00:00
0

here is my query to extract the details

;WITH XMLNAMESPACES ('MerchantInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"' AS mi)
SELECT T.C.value('mi:erpcode[1]','numeric') as erpcode,
T.C.value('mi:SecurityDeposit[1]','varchar(50)') AS securitydeposit
FROM ChangeEvent ce
CROSS APPLY changeddata.nodes('mi/erpcode[1]') AS T(C)
WHERE Ce.EntityTypeId = 2
and
CAST(Ce.ChangedData AS VARCHAR(MAX)) LIKE '%16622710%'
GO

i need the following details

erpcode securitydeposit
16622710 10000

need your help..Thanks in advance..

2

Can you post a sample of your XML data? You did post some raw data without the XML nodes. To be able to extract the data, one needs to know what the XML nodes are and which node contains the data you are looking for.