Home Create XML with XPATH + SQL
Reply: 0

Create XML with XPATH + SQL

user1515
1#
user1515 Published in July 21, 2018, 9:48 am

I need to put something together like the following example that I detail below. This I have to do from my SQL query

Here example:

<Rooms>
  <Room id="1|30#30|23|2017-11-10|1|5453|5451|3|0|0" roomCandidateRefId="1" 
   code="1" description="Standard" nonRefundable="false"/>
</Rooms>

Here what I try

 select 
   n.c.value('(./@id)[1]','varchar(max)') as 'id',
   n.c.value('(./@roomCandidateRefId)[1]','int') as'roomCandidateRefId',                             
   n.c.value('(./@code)[1]','int') as 'code'
from @res.nodes('//Room') as n(c)
for xml path('Room'),root('Rooms')

Any suggestions on how I can do this? Thank you very much for your time.

Example complete:

<Options>
 <Option supplierCode="" type="Hotel" paymentType="MerchantPay" status="OK">
  <Rooms>
     <Room id="1|30|23|2018-012|0|0" roomCandidateRefId="1" code="39" 
     description="Individual" nonRefundable="true"/>
  </Rooms>
</Option>
  <Option type="Hotel" paymentType="MerchantPay" status="OK">
   <Rooms>
     <Room id="1|30|23|2018-01|0|0" roomCandidateRefId="1" code="39" 
    description="Individual" nonRefundable="true"/>
  </Rooms>
 </Option>

I want to get:

<Room id="1|30|23|2018-012|0|0" roomCandidateRefId="1" code="39" 
description="Individual" nonRefundable="true"/>
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.308027 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO