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

Transact-SQL
1

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

2

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?

3

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

4

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

5

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