SQLTeam.com | Weblogs | Forums

Reading XML data in sql server


#1

Hello,

I have given xml data and need parse in to temp table.

declare @rsltacct xml
set @rsltacct= <RsltAcctList>
<RsltAcct>
<IDN_SRCH_RSLT>17948406</IDN_SRCH_RSLT>
<Office>45</Office>
<Value>19</Value>
<Data>
<ACS>
<PLN>xyz</PLN>
<PFN>123</PFN>
<OFF>test</OFF>
</ACS>
</Data>

</RsltAcct>
</RsltAcctList>

Using following query to extract data

SELECT rsltAcct.pos.value('IDN_SRCH_RSLT[1]','bigint') AS IDN_SRCH_RSLT ,
rsltAcct.pos.value('Office[1]','smallint') AS Office,

	rsltAcct.pos.value('Value[1]','decimal(17,2)') AS Value,
	rsltAcct.pos.value('Data[1]','varchar(max)') AS Data
	
	FROM @rsltacct.nodes('RsltAcctList/RsltAcct')  rsltAcct(POS) 

Getting following data

IDN_SRCH_RSLT Office Value Data


17948406 45 19.00 xyz123test

(1 row(s) affected)

I need Data column actual xml value

<ACS><PLN>xyz</PLN> <PFN>123</PFN> lt;OFF>test</OFF> </ACS>----------


#2
....
	rsltAcct.pos.value('Value[1]','decimal(17,2)') AS Value,
	rsltAcct.pos.value('Data[1]','varchar(max)') AS Data,
	rsltAcct.pos.query('./Data/ACS') AS ActualXML  --- <--- THIS
....

#3

Thanks James.