Home Create XML with XPATH + SQL
Reply: 1

Create XML with XPATH + SQL

npalle
1#
npalle Published in 2018-01-10 21:12:04Z

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"/>
Shnugo
2#
Shnugo Reply to 2018-01-11 16:43:01Z

The code you show is a mix of creating XML and reading from XML (with a tendency to reading from).

But according to your question's title you want to create the XML. If my magic crystal ball works well, you'd need this:

DECLARE @rooms_mockup TABLE(id VARCHAR(100),CandidateRefId INT,Code INT, Descr VARCHAR(100),NonRefundable BIT);
INSERT INTO @rooms_mockup VALUES('SomeID 1',1,1,'Description for 1',1)
                               ,('SomeID 2',22,22,'Description for 2',0);
SELECT r.id AS [@id]
      ,r.CandidateRefId AS [@roomCandidateRef]
      ,r.Code AS [@code]
      ,r.Descr AS [@description]
      ,r.NonRefundable AS [@nonRefundable]
FROM @rooms_mockup AS r
FOR XML PATH('Room'),ROOT('Rooms');

The result

<Rooms>
  <Room id="SomeID 1" roomCandidateRef="1" code="1" description="Description for 1" nonRefundable="1" />
  <Room id="SomeID 2" roomCandidateRef="22" code="22" description="Description for 2" nonRefundable="0" />
</Rooms>

Hint:

The id value 1|30#30|23|2017-11-10|1|5453|5451|3|0|0 looks like a clear 1.NF breaker. This can be okay with exported data, but is bad with the physical storage within an RDBMS's table...

UPDATE

According to you updated question you want to get one <Room> out of an XML with many rooms:

DECLARE @xml XML=
N'<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>
 </Options>';

--My example uses a variable with the room's id to get one specific room:

 DECLARE @id VARCHAR(100)='1|30|23|2018-012|0|0';
 SELECT @xml.query(N'/Options/Option/Rooms/Room[@id=sql:variable("@id")]');

--If your surroundings may vary, you can use a deep search

 SELECT @xml.query(N'//Room[@id=sql:variable("@id")]')

Hope this helps...

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO