Parsing XML using Xquery

Question

Parsing XML using Xquery

WangcChiKaBastar

SSCrazy

Points: 2297
More actions
December 8, 2010 at 3:39 am

#95675

DECLARE @doc XML
SET @doc = '<FIXML>
<AllocInstrctn ID="9886809" TransTyp="1" Typ="1" ID2="2049518" RefID="9878217" CxlRplcRsn="99" NoOrdsTyp="1" Side="1" Qty="400000" QtyTyp="0" LastMkt="NOEX" OrignDt="2010-10-15" PxTyp="1" AvgPx="108.23046875" AvgParPx="108.23046875" Ccy="USD" TrdDt="2010-10-15" TxnTm="2010-10-15T12:05:55+01:00" SettlDt="2010-10-20" BkngTyp="0" GrossTrdAmt="432921.88" NetMny="440435.47" PosEfct="O" NumDaysInt="158" AcrdIntRt="0.0187839673913044" AcrdIntAmt="7513.59">
<Hdr>TEST</Hdr>
<CustomTag CustomTradeStatus="Transferred" CustomComments="remove underweight in long end" CustomTrdTm="2010-10-15T12:05:53" CustomAuthDt="2010-10-15T09:01:14.39" CustomStrategy=" " />
<OrdAlloc ClOrdID="9878207" OrdID="62530" ClOrdID2="2049518" />
<Instrmt Sym="T" ID="1018977" Src="101" CFI="" SecTyp="" SubTyp="Fixed" MatDt="2039-11-15" Issued="2009-11-16" Fctr="1" IssuCtry="US" Mult="100" CpnRt="4.375" Exch="NOEX" Issr="United States Of America" Desc="4.375% 15/11/99" IntAcrl="2009-11-15">
<CustomTag CustomDebtType="<Unassigned>" CustomPrice="1.0776560000000002"
CustomLotSize="100" CustomSettlDays="3" CustomSharesOutstanding="44564000000"
CustomPriceCcy="USD" CustomRiskCcy="USD" CustomFirstCoupon="2010-05-15"
CustomAccruedPerShare="0.018783967391304349" CustomInstrumentStatus="1"
CustomIssuerCode="USGOV" CustomAssetClass="Fixed" CustomAssetSubClass="Fixed"
CustomAssetType="Fixed" CustomAssetSubType="Fixed" CustomPayFreq="2" CustomPSA="0"
CustomAccrualType="ACT/ACT" CustomAccrualSubType="" CustomBaseIndexation="0"
CustomRefIndexation="" CustomCheapestToDeliver="" CustomPriceCurve="">
<CustomUCs UC="<Unassigned>" Desc="UBS Account Ref" Id="153" />
<CustomUCs UC="<Unassigned>" Desc="Dexia Account Ref" Id="154" />
<CustomUCs UC="<Unassigned>" Desc="ECP/USCP" Id="185" />
</CustomTag>
<AID AltID="912810QD3" AltIDSrc="1" />
<AID AltID="B59RHH2" AltIDSrc="2" />
<AID AltID="US912810QD37" AltIDSrc="4" />
<AID AltID="T" AltIDSrc="A" />
<AID AltID="B59RHH2" AltIDSrc="102" />
</Instrmt>
<Pty ID="B0291757" Src="B" R="1">
<Sub ID="B0291757" Typ="3" />
</Pty>
<Pty ID="ABC CAPITAL GP (US-FI)" Src="D" R="1">
<Sub ID="B0291757" Typ="3" />
</Pty>
<Pty ID="NOEX" Src="D" R="22" />
<Pty ID="UKFUN55" Src="D" R="11" />
<Pty ID="UKFUN57" Src="D" R="13" />
<Pty ID="UKFUN57" Src="D" R="9" />
<Stip Typ="TEXT" Val="OTHER" />
<Alloc Acct="CLIENT001" ActIDSrc="99" MtchStat="1" Qty="400000" IndAllocID="9886809-ABC CLIENT" ProcCode="0" NotifyBrkrOfCredit="N" HandlInst="3" NetMny="440435.47" AcrdIntAmt="7513.59">
<CustomTag CustomGrossAmt="440435.47">
<CustomUCs UC="<Unassigned>" Desc="IMT-Fax-RBCAcctNo" Id="166" />
<CustomUCs UC="<Unassigned>" Desc="IMT-Fax-RBCDISB" Id="167" />
<CustomUCs UC="<Unassigned>" Desc="IMT-Fax-RBCFedWire" Id="168" />
<CustomUCs UC="<Unassigned>" Desc="IMT-Fax-RBCSWIFT" Id="169" />
<CustomUCs UC="<Unassigned>" Desc="IMT-Fax-UBSaba" Id="170" />
<CustomUCs UC="<Unassigned>" Desc="IMT-Fax-UBSAcctNo" Id="171" />
<CustomUCs UC="<Unassigned>" Desc="IMT-Fax-UBSBrokerName" Id="172" />
<CustomUCs UC="<Unassigned>" Desc="IMT-Fax-UBSBrokerName2" Id="173" />
<CustomUCs UC="<Unassigned>" Desc="IMT-Fax-UBSBic (non-USD)" Id="174" />
<CustomUCs UC="<Unassigned>" Desc="IMT-Fax-UBSBic (USD)" Id="175" />
<CustomUCs UC="0306365 ABC FUND (BUS)" Desc="Portfolio Name" Id="1000" />
<CustomUCs UC="CLIENT001 FUND" Desc="FX Booking" Id="1001" />
<CustomUCs UC="CLIENT001 FUND" Desc="MM Booking" Id="1002" />
<CustomUCs UC="HK01234567" Desc="Booking Reference" Id="1003" />
<CustomUCs UC="0306365" Desc="IMS Portfolio Code" Id="1006" />
</CustomTag>
</Alloc>
</AllocInstrctn>
</FIXML>'
SELECT
FIXML.ID.value('/Pty/@ID','VARCHAR(20)') as 'PTY ID' ,
FIXML.ID.value('/Pty/@Src','VARCHAR(20)') as 'PTY Src',
FIXML.ID.value('/Instrmt/AID/@AltID','VARCHAR(20)') as 'AltID' ,
FIXML.ID.value('/Instrmt/AID/@AltIDSrc','VARCHAR(20)') as 'AltIDSrc'
FROM
@doc.nodes('/FIXML/AllocInstrctn') as FIXML(ID)
In the Query above I am trying to extract all the values for PTY ID, PTY SRC and AltID and AltIDSrc but the code is throwing error.
Please suggest.

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

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

A couple things:

1. < and > are not allowed in attribute values. Change all of the "<Unassigned>" values to "& lt;Unassigned& gt;" (remove the spaces between "&" and "lt" and "gt"... forum formatting fun...), and you'll get past the error you're seeing.

2. Your .nodes will only return a single node, the <AllocInstrctn/> node. It appears you want two different nodesets: all of the <Pty/> nodes, and all of the <Instrmt><AID/></Instrmt> nodes. These are unrelated in the source document, so a single resultset is rather hard to construct... which AltID should be in the row with Pty ID "NOEX"? Not seeing any relation, I wrote something that would return the two sets of data from a corrected (see point #1) XML source:

SELECT FIXMLPty.ID.value('(../@ID)[1]','VARCHAR(20)') AS 'Alloc ID',

FIXMLPty.ID.value('(./@ID)[1]','VARCHAR(20)') AS 'PTY ID',

FIXMLPty.ID.value('(./@Src)[1]','VARCHAR(20)') AS 'PTY Src'

FROM @doc.nodes('/FIXML/AllocInstrctn/Pty') AS FIXMLPty(ID)

SELECT FIXMLAID.ID.value('(../../@ID)[1]','VARCHAR(20)') AS 'Alloc ID',

FIXMLAID.ID.value('(./@AltID)[1]','VARCHAR(20)') AS 'AltID',

FIXMLAID.ID.value('(./@AltIDSrc)[1]','VARCHAR(20)') AS 'AltIDSrc'

FROM @doc.nodes('/FIXML/AllocInstrctn/Instrmt/AID') AS FIXMLAID(ID)

Eddie Wuerch
MCM: SQL

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 2

<Snip>Previous post covered it</Snip>

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537