sql - Force Empty Decimal Placeholders in Decimal SimpleType -
I have XML which produces monetary quantities, which is often the result of currency conversions and therefore showed them well Degree of accuracy should be done (in this case, 4 dp). I came to know that when I apply XML with the numbers listed, as soon as I apply a schema on XML, all the "unnecessary" 0 points are removed. Unless I can understand it from a computer's perspective, we need to show our customers that regardless of the value of each number our number accuracy is in all digits. For their understanding, it is very clear if we are consistent in that, we always show 4 decimal places at such numbers. Here's an example that you can see what I mean (compare the results before and after):
/ * This schema will create a schema archive, use it and leave it , So * / DECLARE @XSD XML set @ xsd = convert (XML, '' xml version = "1.0" encoding = "UTF-8"? Gt; xml-stylesheet type = "text / Xsl "href =" xs3p.xsl "? & Gt; & lt; xs: schema id =" MyData "targetNamespace =" http://tempuri.org/MyData.xsd "elementFormDefault =" Qualified "xmlns =" http : // Tempuri.org/MyData.xsd "xmlns: mstns =" http://tempuri.org/MyData.xsd "xmlns: xs =" http: / /www.w3.org/2001/XMLSchema "& gt; & lt; xs: Simple Type Name =" Money "& gt; & lt; xs: Restriction Base =" Xs: Decimal "& gt; & lt; x: Decrease total value = "14" /> <: Excerpt: Digit value = "4" /> gt; gt; complex type> gt;: xs: sequence & gt; & lt; xs: element name = "amount" type = "money" MinOckers = "1" Max Okorks = "unbalanced" & gt; & Lt; / Xs: element & gt; & Lt; / Xs: sequence & gt; & Lt; / XS: complexType & gt; & Lt; / XS: element & gt; & Lt; / Xs: schema & gt; ') - If the spaces are desired to clean (select * FROM sys.xml_schema_collections WHERE name =' TestMoney ') DROP XML SCHEMA COLLECTION TestMoney - Use our XML bits to create a new schema archive For XML Scheema compilation TestMoney AS @XSD go - we will build our XML first, so we can check it before changing DECLARE @Untyped_XML XML - This schema is implemented with DECLARE @XML is the real test XML (Testmany) - Some test data Specify @Untyped_XML = '& lt ;? XML version = "1.0"? & Gt; & Lt; Root xmlns = "http://tempuri.org/MyData.xsd" & gt; & Lt; Amount & gt; 1.0000 & lt; / Zodiac & gt; & Lt; Amount & gt; 0.1234 & lt; / Zodiac & gt; & Lt; / Root & gt; '- Try to apply schema collection to our XML, and show anything useful. BEGIN SET @XML = @Untyped_XML SELECT @Untyped_XML AS, @XML AS [finally] Select the catch as check ERROR_MESSAGE (), @Untyped_XML AS FailedXML Finally catch - we do not really want this schema Are; Can anyone show up on exact accuracy?
OK, it seems that there is no easy way to do this - my solution is because To validate to try only to fix this problem, XML again, if there is no error in verification, then I think that by typing untyped_XML successfully and it is correct Can return. It's a downside to continue to work with untyped xml, but this means that at least it looks like the schema measurement. To do this, I have replaced TRY / CATCH in this question: - Try to apply the schema collection to our XML, and show whatever is useful. Select BEGIN select @xml = @UNTYPED_XML Finally select the catch @SchemaError = ERROR_MESSAGE () END Catch - If there was no error, this is because the schema is correctly valid, so show the original file IF @SchemaError is NULL SELECT @Untyped_XML - If you have any errors, display it, select with bad XML @SchemaError AS 'ErrorEncountered', @Untyped_XML AS FailedXML GO This is unsatisfactory, but It seems to me that the problem is solved and it seems like the best I can do for XML After receiving without giving effect to Tailshit (in Ssais, possibly using an XML functions) have been prepared.
Comments
Post a Comment