CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlxpath
npalle
npalle

Reputation: 71

Create XML with XPATH + SQL

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"/>

Upvotes: 1

Views: 256

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

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...

Upvotes: 1

Related Questions