How to extract date value (in MM/DD/YYYY format) from XML field value?

Hi All,

I am having an XML field in a table "RMRECC" (field name: "Amount") and the value of the field is given below.

<X C="0" I="1">
<E D="1Y0M0W0D" P="0" A="0.05" />
<E D="1Y0M0W0D" P="0" A="0" />
<E D="1Y0M0W0D" P="0" A="0.03" />
</X>

I need to extract the date value "1Y0M0W0D" in MM/DD/YYYY format.

Can anybody tell me how this can be handled in SQL query?

Thanks in advance.

Regards,

Sriram.T.S
NTrust Infotech pvt. ltd

This will extract the value, you should be able to do any transformations on it after

DECLARE @MyXML XML
SET @MyXML = '<X C="0" I="1">
<E D="1Y0M0W0D" P="0" A="0.05" />
<E D="1Y0M0W0D" P="0" A="0" />
<E D="1Y0M0W0D" P="0" A="0.03" />
</X>
';

SELECT	X.Y.value('E[1]/@D','VARCHAR(10)') AS DT
FROM	@MyXML.nodes('X') AS X(Y);

Do you have an example of the format? is Y full year?

Hi Doshan,

Thanks for the reply.

I have already extracted the XML values separately. I just want to extract the date value (i.e. convert the date into MM/DD/YYYY format) from the source format.

I am not sure, in what format the source is having the value (1Y0M0W0D).

I am suspecting that it is the default date format for XML field in SQL Server.

Please help me on this.

Regards,

Sriram.T.S

Is this a date value or is it measuring duration? i.e. 1 Year 0 Months 0 Weeks 0 Days

I am not sure about that.
I thought that, this is the default date format of XML field in SQL Server.