Create XML with XPATH + SQL

Question

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

I don't get it... Are you trying to build the XML by a SQL query? Are you trying to read from an XML? What is @res? Where is the data coming from (table, variable, file upload, ...)? — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 11, 2018 at 8:31
Yes, I'm trying to build (replicate) an xml using sql. I receive an xml that contains among other things the node Rooms, @res would be the variable in which I save xml that they send me, I need to get the complete node Room (save it as a variable and send it to another xml). All this from a stored procedure sql. Sorry, if I was not clear — npalle
– npalle, Commented Jan 11, 2018 at 13:57
So: There is some XML coming in with many Rooms and you want to pick one room and create a new XML with this? Correct? Is the incoming XML oft the same structure or entirely different? Please provide a (reduced) example of your input XML and the needed output you'd expect. — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 11, 2018 at 14:31
Yes, they send many Rooms. I want to get the Room node in a variable and then add it in another XML. In the main post I leave the complete example and the result I want. Thank you — npalle
– npalle, Commented Jan 11, 2018 at 15:13
npalle, see my updated answer. You did not tell anything about your second step (add it to another XML), but I'd suggest to start a new question with this - if you need help. — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 11, 2018 at 16:44

Gottfried Lesigang · Accepted Answer · 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...

Collectives™ on Stack Overflow

Create XML with XPATH + SQL

1 Answer 1

Hint:

UPDATE

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Hint:

UPDATE

Comments

Your Answer

Sign up or log in

Post as a guest

Related