Вот как выглядит мой XML:
<Product sequence_number="1" number="1543448904" id="S1" unit_number="1">
<consumer_narrative name="GLENN,GREGORY" date_filed="02/13/2009">
<message type="Consumer Comments">THE CONSUMER STATES THIS WAS NOT </message>
<message type="Consumer Comments">THE PRODUCT REQUESTED.</message>
</consumer_narrative></Product> <Product sequence_number="2" number="1543448905" id="S1" unit_number="1">
<consumer_narrative name="JOHN,GORDON" date_filed="08/23/2009">
<message type="Consumer Comments">THE CONSUMER STATES THAT</message>
<message type="Consumer Comments">WRONG PRODUCT WAS SENT.</message>
</consumer_narrative> </Product>
Мой запрос:
SELECT
tab.col.value('../@number', 'varchar(30)') [Claim Number],
tab.col.value('../@name', 'varchar(30)') [Name],
tab.col.value('../@date_filed', 'varchar(30)') [DateField],
tab.col.value('@type', 'varchar(50)') [Type],
tab.col.value('.', 'varchar(250)') [CustomerComments]
FROM XMLTABLE AS B
CROSS APPLY xmldocument.nodes('//Product/consumer_narrative/message') tab(col)
WHERE B.XMLId = 123
Дает мне «ноль» для номера претензии. Что я должен иметь вместо ../@number, чтобы получить номер претензии.