Reputation: 71
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
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>
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...
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