XML To SQL Data Types

SSIS and Import/Export
1

Hi Guys,

I am pulling a data from XML To SQL through SSIS (Using XML Source). Source field in XML has data type xs:duration and value are as an example "PT00H30M" OR "PT01H00M". However
it is coming through in my destination table as something like "6000000000" or "27000000000".

Any help would be great appreciated.

Thank You

2

To the best of my knowledge, that format isn't some magical XML representation of a duration. While I have no idea what the "PT" embedded label is, "H" and "M" are likely "Hours" and "Minutes" respectively and will need to be teased apart using SUBSTRING.

3

"PnYnMnDTnHnMnS" synax appears to be duration data type in XML. I don't know whether SSIS XML Source can handle that or not.

http://www.w3schools.com/xml/schema_dtypes_date.asp

4

Great link, James. Thanks for that and I stand corrected. I agree... I don't know whether SSIS XML Source does the transformation correctly or not.