Need to extract Data from XML

Question

Post reply

Need to extract Data from XML

WangcChiKaBastar

SSCrazy

Points: 2297
More actions
September 16, 2011 at 3:28 am

#73815

Hi Guys,

Please advise how can I extract data from all the attributes under DETAIL section from the following XML using XQUERY

<MESSAGE>

<HEADER>

<Source_System>ACF</Source_System>

<Target_System>CRIMS</Target_System>

<System_Date>2011/01/14</System_Date>

<Generation_Date>2011/09/05</Generation_Date>

<Generation_Time>11:25:28</Generation_Time>

</HEADER>

<DETAIL>

<Account_CD>VPBN</Account_CD>

<Source_Security_ID>131924</Source_Security_ID>

<Long_Short_Code>L</Long_Short_Code>

<Valuation_Date>2011/01/14</Valuation_Date>

<Qty>N</Qty>

<Tax_lot_ID>N</Tax_lot_ID>

<Position_Or_TaxLot>P</Position_Or_TaxLot>

<Desc>última flor do Lácio</Desc>

<Source>ACF</Source>

</DETAIL>

<TRAILER>

<Number_of_Records>N</Number_of_Records>

</TRAILER>

</MESSAGE>

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

DECLARE @XML XML = '<MESSAGE>



<HEADER>

<Source_System>ACF</Source_System>

<Target_System>CRIMS</Target_System>

<System_Date>2011/01/14</System_Date>

<Generation_Date>2011/09/05</Generation_Date>

<Generation_Time>11:25:28</Generation_Time>

</HEADER>

<DETAIL>

<Account_CD>VPBN</Account_CD>

<Source_Security_ID>131924</Source_Security_ID>

<Long_Short_Code>L</Long_Short_Code>

<Valuation_Date>2011/01/14</Valuation_Date>

<Qty>N</Qty>

<Tax_lot_ID>N</Tax_lot_ID>

<Position_Or_TaxLot>P</Position_Or_TaxLot>

<Desc>última flor do Lácio</Desc>

<Source>ACF</Source>

</DETAIL>

<TRAILER>

<Number_of_Records>N</Number_of_Records>

</TRAILER>

</MESSAGE>';

SELECT @XML.value('(/MESSAGE/DETAIL/Account_CD/text())[1]','varchar(100)') AS Account_CD;

Just add the rest of the columns you need by following that pattern.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

paul_ramster Ten Centuries Points: 1382 More actions · Answer 2

you can grab the whole lot like this:



DECLARE @XML XML = '<MESSAGE>

<HEADER><Source_System>ACF</Source_System><Target_System>CRIMS</Target_System><System_Date>2011/01/14</System_Date><Generation_Date>2011/09/05</Generation_Date>

<Generation_Time>11:25:28</Generation_Time></HEADER><DETAIL><Account_CD>VPBN</Account_CD><Source_Security_ID>131924</Source_Security_ID><Long_Short_Code>L</Long_Short_Code><Valuation_Date>2011/01/14</Valuation_Date><Qty>N</Qty><Tax_lot_ID>N</Tax_lot_ID><Position_Or_TaxLot>P</Position_Or_TaxLot><Desc>última flor do Lácio</Desc><Source>ACF</Source></DETAIL><TRAILER><Number_of_Records>N</Number_of_Records></TRAILER></MESSAGE>';

select x.Y.value('local-name(.)','varchar(30)') as [node], x.Y.value('.','varchar(50)') as [value]

from @XML.nodes('/MESSAGE/DETAIL/*') as x(Y)