CODEWITHSUNDEEP
sqlsql-server
user3290807
user3290807

Reputation: 391

XML Join in sql server

I am trying to join the two xml response in stored procedure with the below mentioned code .The output that i was looking from my select query was 

declare @cdoValuation xml
declare @marketColorPrice xml

set @cdoValuation = '<Response>
  <CDOValuation id="BAB12-II.A2" mv="1.019358126500000e+002" run_date="2014-04-30T00:00:00" />
  <CDOValuation id="BABS12-I.C" mv="9.915358793000000e+001" run_date="2014-04-30T00:00:00" />
  <CDOValuation id="BLUEMTN.A2" mv="9.925446292000000e+001" run_date="2014-04-30T00:00:00" />
  <CDOValuation id="BLUEMTN3.C" mv="9.472908099999999e+001" run_date="2014-04-30T00:00:00" />
  <CDOValuation id="CGMS11-1.D" mv="9.644486014000000e+001" run_date="2014-04-30T00:00:00" />
  <CDOValuation id="CSQUARE4.A" mv="9.880319818000000e+001" run_date="2014-04-30T00:00:00" />
  <CDOValuation id="CSQUARE4.C" mv="9.295238056000000e+001" run_date="2014-04-30T00:00:00" />
  <CDOValuation id="MADPK5.C" mv="9.735145883000000e+001" run_date="2014-04-30T00:00:00" />
  <CDOValuation id="MADPK7.PS" mv="9.225345985000000e+001" run_date="2014-04-30T00:00:00" />
  <CDOValuation id="MADPK8.A" mv="9.984445828000000e+001" run_date="2014-04-30T00:00:00" />
  <CDOValuation id="OHAPARK.B" mv="9.652910265000000e+001" run_date="2014-04-30T00:00:00" />
  <CDOValuation id="SDAYTONA.B2L" mv="9.590162241000000e+001" run_date="2014-04-30T00:00:00" />
</Response>'

set @marketColorPrice = '<Response>
  <MarketColorPrice tranche_id="BAB12-II.A2" mk_price="1.001533333333333e+002" mk_date="2013-12-19T00:00:00" mk_source1="Empirasign/SCI" mk_source2="Cvr, Talk" mk_record_type="bwic, pxtalk" />
  <MarketColorPrice tranche_id="BLUEMTN.A2" mk_price="9.877000000000000e+001" mk_date="2013-12-16T00:00:00" mk_source1="Empirasign/SCI" mk_source2="Cvr, Talk" mk_record_type="bwic, pxtalk" />
  <MarketColorPrice tranche_id="BLUEMTN3.C" mk_price="8.050000000000000e+001" mk_date="2013-05-15T00:00:00" mk_source1="Empirasign/SCI" mk_source2="Talk, Trade" mk_record_type="bwic, pxtalk" />
  <MarketColorPrice tranche_id="CSQUARE4.A" mk_price="9.765294117647059e+001" mk_date="2014-04-08T00:00:00" mk_source1="Empirasign/SCI" mk_source2="Cvr, Talk" mk_record_type="bwic, pxtalk" />
  <MarketColorPrice tranche_id="CSQUARE4.C" mk_price="9.125000000000000e+001" mk_date="2014-05-15T00:00:00" mk_source1="Empirasign" mk_source2="Talk" mk_record_type="market" />
  <MarketColorPrice tranche_id="MADPK5.C" mk_price="9.200000000000000e+001" mk_date="2013-10-24T00:00:00" mk_source1="SCI" mk_source2="Cvr" />
  <MarketColorPrice tranche_id="MADPK7.PS" mk_price="1.133333333333333e+002" mk_date="2013-08-28T00:00:00" mk_source1="Empirasign/SCI" mk_source2="Cvr, Talk" mk_record_type="bwic, pxtalk" />
  <MarketColorPrice tranche_id="OHAPARK.B" mk_price="9.787500000000000e+001" mk_date="2014-04-21T00:00:00" mk_source1="Empirasign" mk_source2="Talk" mk_record_type="market" />
</Response>'

declare @responseXml xml
set @responseXml =(select   cdoValuation.cdoValuationcol.value('(//CDOValuation/@mv)[1]', 'float') as "@mv",
        dbo.ToShortDateString(cdoValuation.cdoValuationcol.value('(//CDOValuation/@run_date)[1]', 'datetime')) as "@run_date",
        marketColorPrice.marketColorPricecol.value('(//MarketColorPrice/@mk_price)[1]', 'float') as "@mk_price",
        marketColorPrice.marketColorPricecol.value('(//MarketColorPrice/@mk_source1)[1]', 'varchar(30)') as "@mk_source1",
        marketColorPrice.marketColorPricecol.value('(//MarketColorPrice/@mk_source2)[1]', 'varchar(30)') as "@mk_source2"
from @cdoValuation.nodes('/CDOValuation') cdoValuation(cdoValuationcol) 
inner join @marketColorPrice.nodes('/MarketColorPrice') marketColorPrice(marketColorPricecol) 
on marketColorPrice.marketColorPricecol.value('(//MarketColorPrice/tranche_id)[1]', 'VARCHAR(20)')  = 
cdoValuation.cdoValuationcol.value('(//CDOValuation/id)[1]', 'VARCHAR(20)')     FOR XML PATH('BWICResult')

        , ROOT('Response'))

        select @responseXml

The output that i was expecting was of the following format but doesnt seems to be the case .Can someone help me out to know as to what should be done ?

  <Response> <BWICResult tranche_id="BAB12-II.A2" mv="1.019358126500000e+002" mk_price="1.001533333333333e+002"/> <BWICResult tranche_id="BLUEMTN.A2" mv="9.925446292000000e+001" mk_price="9.877000000000000e+001"/> </Response>

Upvotes: 0

Views: 66

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

select C.X.value('@id', 'varchar(20)') as '@id',
       C.X.value('@mv', 'float') as '@mv',
       M.X.value('@mk_price', 'float') as '@mk_price'
from @cdoValuation.nodes('/Response/CDOValuation') as C(X)
  inner join @marketColorPrice.nodes('Response/MarketColorPrice') as M(X)
    on C.X.value('@id', 'varchar(20)') = M.X.value('@tranche_id', 'varchar(20)')
for xml path('BWICResult'), root('Response')

Result:

<Response>
  <BWICResult id="BAB12-II.A2" mv="1.019358126500000e+002" mk_price="1.001533333333333e+002" />
  <BWICResult id="BLUEMTN.A2" mv="9.925446292000000e+001" mk_price="9.877000000000000e+001" />
  <BWICResult id="BLUEMTN3.C" mv="9.472908099999999e+001" mk_price="8.050000000000000e+001" />
  <BWICResult id="CSQUARE4.A" mv="9.880319818000000e+001" mk_price="9.765294117647059e+001" />
  <BWICResult id="CSQUARE4.C" mv="9.295238056000000e+001" mk_price="9.125000000000000e+001" />
  <BWICResult id="MADPK5.C" mv="9.735145883000000e+001" mk_price="9.200000000000000e+001" />
  <BWICResult id="MADPK7.PS" mv="9.225345985000000e+001" mk_price="1.133333333333333e+002" />
  <BWICResult id="OHAPARK.B" mv="9.652910265000000e+001" mk_price="9.787500000000000e+001" />
</Response>

Upvotes: 1

Related Questions