Skip to main content

Posts

T SQL : Querying XML with Attributes takes so long to load with Resolution

-- This will take time to load up (almost FOREVER?) :( SELECT * FROM  (SELECT [TestId] = 999   -- Attribute(s): ,col.value('../@JeepNumber', 'varchar(10)') as JeepNumber ,col.value('../@AccountPeriod', 'varchar(10)') as AccountPeriod ,col.value('../@TotalNonConformityDeviation', 'decimal(18,2)') as TotalNonConformityDeviation ,col.value('../@TotalWorkhours', 'decimal(18,2)') as TotalWorkhours -- Normal column(s): ,col.value('data(PersonEmployeeId[1])', 'int') as PersonEmployeeId ,col.value('data(RankCode[1])', 'varchar(10)') as RankCode ,col.value('data(PeriodDate[1])', 'varchar(10)') as PeriodDate ,col.value('data(Comments[1])', 'varchar(max)') as Comments ,col.value('data(RestHourApplicableIn24hHourPeriod[1])', 'decimal(18,2)') as RestHourApplicableIn24hHourPeriod ,col.value(...

MS SQL - How to read xml data with multiple namespaces

The XML: <?xml version="1.0"?>-<ns0:usp_Moodlerooms_Cache_GetResponse xmlns:ns0=" http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/BizTalk " xmlns:ns3=" http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/BizTalk/usp_Moodlerooms_Cache_Get " xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance "> -<ns0:StoredProcedureResultSet0> -<ns3:StoredProcedureResultSet0><ns3:SSO_UserId>3632303</ns3:SSO_UserId><ns3:SSO_UserName> maveales@yahoo.com </ns3:SSO_UserName><ns3:UserName> maveales@yahoo.com </ns3:UserName><ns3:FirstName>Mave</ns3:FirstName><ns3:MiddleName>Bent</ns3:MiddleName><ns3:LastName>Moras</ns3:LastName><ns3:EmailAddress> maveales@yahoo.com </ns3:EmailAddress><ns3:City>Esta City</ns3:City><ns3:Country>Philippines</ns3:Country><ns3:CompetenceName>0089 - Medical Care</ns3:CompetenceName...