I am being trying to understand and learn how to take value from XML. I have taken a reference of an XML document from the link below.
In order to get clarification I have removed some of the unwanted xml parts.
Example
<?Label VCAT|RESERVATION|21405|SUCCESS?>
<Reservation xmlns="reservation.fidelio.4.0" mfShareAction="NA" mfReservationAction="EDIT">
<reservationID>11650</reservationID>
<GuestCounts>
<GuestCount>
<ageQualifyingCode>ADULT</ageQualifyingCode>
<mfCount>1</mfCount>
</GuestCount>
<GuestCount>
<ageQualifyingCode>CHILD</ageQualifyingCode>
<mfCount>0</mfCount>
</GuestCount>
</GuestCounts>
</Reservation>
In HTML, For Example google.com - To target input for that Page we use XPath as //input[@name='q']. Similarly inorder to target a node we can specify //ageQualifyingCode to get value within that node. So based on that I have created a query specified below. Can anyone tell me what's wrong with this query as I am getting output as NULL.
DECLARE @XmlDocumentHandle int
DECLARE @XMLData XML
SET @XMLData ='<?Label VCAT|RESERVATION|21405|SUCCESS?>
<Reservation xmlns="reservation.fidelio.4.0" mfShareAction="NA" mfReservationAction="EDIT">
<reservationID>11650</reservationID>
<GuestCounts>
<GuestCount>
<ageQualifyingCode>ADULT</ageQualifyingCode>
<mfCount>1</mfCount>
</GuestCount>
<GuestCount>
<ageQualifyingCode>CHILD</ageQualifyingCode>
<mfCount>0</mfCount>
</GuestCount>
</GuestCounts>
</Reservation>'
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XMLData
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/Reservation/GuestCounts/GuestCount/',2)
WITH (ageQualifyingCode varchar(10) '//ageQualifyingCode',
mfCount int '//mfCount')
EXEC sp_xml_removedocument @XmlDocumentHandle
I have also tried using XQuery mentioned below but no result
SELECT * FROM (
SELECT TOP 1
X.Y.value('(ageQualifyingCode)[1]', 'nvarchar(50)') AS ageQualifyingCode, X.Y.value('(mfCount)[1]', 'int') AS mfCount
FROM #temp1 t
CROSS APPLY t.xmlBody.nodes('Reservation/GuestCounts/GuestCount') AS X(Y)
) T
Also, how will I be able to target reservationID in same cross apply?
Output
+---------------+-------------------+---------+
| reservationID | ageQualifyingCode | mfCount |
+---------------+-------------------+---------+
| 11650 | ADULT | 1 |
+---------------+-------------------+---------+
| 11650 | CHILD | 0 |
+---------------+-------------------+---------+
