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
Dohsan
October 5, 2015, 7:31am
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?
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
Dohsan
October 5, 2015, 9:29am
4
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.