[T-SQL] Importing XML file into SQL 2008R2...nearly there |
- Importing XML file into SQL 2008R2...nearly there
- Year to Date Query ... By month?
- need help and getting unique set
- Convert character data to decimal
- unable to add diagrams
| Importing XML file into SQL 2008R2...nearly there Posted: 20 Aug 2013 12:29 AM PDT Hi,Trying to import a file with the structure:<PetrolPrices> [left]<Fuel type="Super Unleaded"> <Average units="p">145.83</Average> </Fuel> <Fuel type="Unleaded"> <Average units="p">137.42</Average> </Fuel> <Fuel type="Premium Diesel"> <Average units="p">151.63</Average> </Fuel> <Fuel type="Diesel"> <Average units="p">141.64</Average> </Fuel> <Fuel type="LPG"> <Average units="p">70.57</Average> </Fuel>[/left]</PetrolPrices>Using:select c3.value('Fuel[1]','Varchar(30)') as [Fuel Type], c3.value('Average[1]','decimal(18,3)') as [Average Units] from( select cast(c1 as xml) from OPENROWSET (BULK 'C:\FuelCosts\UK averages_20130819.xml',SINGLE_BLOB) as T1(c1))as T2(c2)cross apply c2.nodes('/PetrolPrices/Fuel') T3(c3)Which gives me:Fuel Type Average UnitsNULL 145.830NULL 137.420NULL 151.630NULL 141.640NULL 70.570Can someone help me out with what I am doing wrong for the Fuel Type please.many thanksMark |
| Year to Date Query ... By month? Posted: 20 Aug 2013 12:44 AM PDT Hi, I have a table which currently has a month and a number by month representing lets say car sales,Table CarMonth - DateSales - Int I would like to get a Year To Date formula going which i understand how to do for one value for the most recent month Sum(Sales) from Car Where Month >= '2012-04-01' however its getting complicated because i need to show the year to date data by month, so i need to create some sort of loop to go though every month?Example:April 2012 - #=5 YTD=5May 2012 - #=10 YTD=15June 2012 - #=2 YTD=17any help is greatly appreciated |
| need help and getting unique set Posted: 19 Aug 2013 06:19 AM PDT Hi all,I need a help on getting the distinct set.My Data look like this:SET 1SET ID Product Attribute1 A 101 A 11SET 2SET ID Product Attribute2 A 102 A 12SET 3SET ID Product Attribute3 A 103 A 11Since set ID 1 and 3 has the same product and attribute I just need to get distinct set having the same product and attribute. The result set will look like thisSET ID Product Attribute1 A 101 A 112 A 102 A 12Thank you all in advance for your help. |
| Convert character data to decimal Posted: 19 Aug 2013 03:17 AM PDT I have some data coming in with leading 0s as well a +/-.You can see what I have tried so far. I don't understand why the negative numbers are not being handled correctly.Thanks if you have a suggestion or a better way to do this.[code="sql"]CREATE TABLE #Test(TestData varchar(50))INSERT INTO #Test SELECT '+000000000000760.00'INSERT INTO #Test SELECT '+000004532078501.60'INSERT INTO #Test SELECT '-000000001224249.00'INSERT INTO #Test SELECT '+000000000468181.00'SELECT * FROM #TestSELECT CASE LEFT(TestData,1)WHEN '+' THEN CONVERT(decimal (18,2),REPLACE(LTRIM(REPLACE(TestData,'0',' ')),' ','0')) WHEN '-' THEN - CONVERT(decimal (18,2),REPLACE(LTRIM(REPLACE(TestData,'0',' ')),' ','0')) END FROM #TestDROP TABLE #Test[/code] |
| Posted: 19 Aug 2013 06:40 AM PDT When I try to add it I get error saying that the database needs valid owner.On the "General" screen there is an Active Directory owner.but on the Files screen the owner is blank.Why is that and why do I need to have an owner there?Thanks,Tom |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment