September 4, 2015 at 8:24 am
A recent SharePoint upgrade has rendered several views obsolete. I am redefining them so that our upper level executive reports show valid data.
(yes, I know that doing anything to sharepoint could cause MS to deny support, having said that, this is something I've inherited and need to fix, pronto)
The old view was created like so:
USE [AHMC]
GO
/****** Object: View [dbo].[vwSurgicalVolumes] Script Date: 09/04/2015 09:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwSurgicalVolumes] AS
SELECT
a.tp_ID AS ListID,
a.tp_WebId AS WebID,
a.tp_Title AS ListName,
b.FullUrl AS SiteURL,
b.Title AS SiteTitle,
DATEADD(DAY, 0, DATEDIFF(DAY, 0, c.datetime1)) AS ServiceDate,
CAST(DATEPART(yyyy, c.datetime1) AS varchar(4))+ '-' +
CAST(REPLICATE('0', 2-LEN(MONTH(c.datetime1))) AS varchar(2)) +
CAST(DATEPART(MONTH, c.datetime1) AS varchar(2)) AS DateYYYYMM,
CAST(DATEPART(yyyy, c.datetime1) AS varchar(4))AS DateYYYY,
CAST(DATEPART(MONTH, c.datetime1) AS INT) AS DateMM,
c.float1 AS InHouseCases,
c.float2 AS InHouseMinutes,
c.float3 AS OutPatientCases,
c.float4 AS OutPatientMinutes,
c.sql_variant1 AS TotalCases,
c.sql_variant2 AS TotalMinutes
FROM AHPORTAL.WSS_AHPortal_Content_01.dbo.AllLists a
JOIN AHPORTAL.WSS_AHPortal_Content_01.dbo.Webs b
ON a.tp_WebId=b.Id
JOIN AHPORTAL.WSS_AHPortal_Content_01.dbo.AllUserData c
ON c.tp_ListId=a.tp_ID
WHERE a.tp_ID = 'AE31E0F2-529D-447D-92F5-9AB1B830CD33'
AND c.tp_DeleteTransactionId=0x
GO
As I said, this view is used in a report showing surgical minutes.
SharePoint is now on a new server, which is linked differently (distributed?) I've used OPENQUERY to get my 'new' query to work;
SELECT *
FROM OPENQUERY ([PORTALWEBDB], 'SELECT
--AllLists
AL.tp_ID AS ALtpID
,AL.tp_WebID as altpwebid
,AL.tp_Title AS ALTitle
--Webs
,AW.FullURL
,AW.Title
--AllUserData
,AUD.tp_ID
,AUD.tp_ListId
,AUD.tp_SiteId
,AUD.tp_Modified
,AUD.tp_Created
,AUD.tp_Ordering
,AUD.tp_ItemOrder
,AUD.tp_ParentId
,AUD.tp_DocId
,AUD.tp_DeleteTransactionId
,AUD.tp_ContentTypeId
,AUD.tp_Level
,CAST(AUD.tp_ColumnSet AS Varchar(MAX)) AS XML_Data
FROM [WSS_AHPortal_Content_01].[dbo].[AllLists] AL
JOIN [WSS_AHPortal_Content_01].[dbo].[Webs] AW
ON AL.tp_WebId=AW.Id
JOIN [WSS_AHPortal_Content_01].[dbo].[AllUserData] AUD
ON AUD.tp_ListId=AL.tp_ID
WHERE AL.tp_WebID = ''86A719BF-CF70-405D-9CAE-1CFADE45B5AE''
AND AL.tp_ID = ''AE31E0F2-529D-447D-92F5-9AB1B830CD33''
AND AUD.tp_DeleteTransactionId = 0x
AND AUD.tp_Created > ''2014-08-15''
')
GO
My data (ie surgical minutes, etc) seems to be in the XML column, AUD.tp_ColumnSet . So I need to parse it out and convert it to INT to maintain consistency with the previous view. How do I do this within the context of the view definition?
Here is a representation of the new and old view data copied to excel :
<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" xsi:type="sqltypes:float">1.800000000000000e+001</sql_variant1><sql_variant2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" xsi:type="sqltypes:float">1.230000000000000e+003</sql_variant2>
Old view copied to excel (I pulled out the lengthy id and title columns as well as the extraneous date parts):
old view
ServiceDate InHouseCasesInHouseMinutesOutPatientCasesOutPatientMinutesTotalCasesTotalMinutes
8/14/2014 0:002 41916811181230
Ugh, can't format it to make it look decent. InHouseCases =2, InHouseMinutes=419, OutPatientCases =16, OutPatientMinutes=1230. This corresponds to the new data I can see in the XML column; 2.000000000000000e+000 is indeed 2 and 4.190000000000000e_002 is indeed 419.
Please help, I'm a bit lost. Even OPENQUERY is new to me. Thanks!
September 4, 2015 at 9:05 am
I have found that this XML.Value code will get me the first 4 of the 6 pieces of data I need:
DECLARE @x xml
SET @x = '<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" xsi:type="sqltypes:float">1.800000000000000e+001</sql_variant1><sql_variant2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" xsi:type="sqltypes:float">1.230000000000000e+003</sql_variant2>
'
SELECT @x.value(
'(float1)[1]','FLOAT')
,@x.value(
'(float2)[1]','FLOAT')
,@x.value(
'(float3)[1]','FLOAT')
,@x.value(
'(float4)[1]','FLOAT')
Now I think I just need help with those last 2, SQL-Variants
September 4, 2015 at 2:22 pm
Add this to the end?
,@x.value(
'(sql_variant1)[1]','FLOAT')
,@x.value(
'(sql_variant2)[1]','FLOAT')
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
September 4, 2015 at 5:39 pm
The other have told you how to parse it. That's good because I just want to rant a bit and I know none of it is your fault. Like I said, just ranting.
The XML takes 600 characters and even as text, the total number of bytes required for the actual data including delimiters is only 40 characters. That's 14X more data to run through the pipe thanks only to tag and type bloat. And people wonder why they have IO problems.
As a DBA, Developer, and logic human being, XML does not now nor will it ever be a justifiably good thing in any of the databases I'm responsible for. Heh... SharePoint was bad enough without it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2015 at 6:17 am
Thank You Mike, that was it!
The other have told you how to parse it. That's good because I just want to rant a bit and I know none of it is your fault. Like I said, just ranting.
The XML takes 600 characters and even as text, the total number of bytes required for the actual data including delimiters is only 40 characters. That's 14X more data to run through the pipe thanks only to tag and type bloat. And people wonder why they have IO problems.
As a DBA, Developer, and logic human being, XML does not now nor will it ever be a justifiably good thing in any of the databases I'm responsible for. Heh... SharePoint was bad enough without it.
--Jeff Moden
The really odd(to me) thing is that I've been told that this data is loaded into SharePoint via some unknown mechanism from Meditech (our hospital info system). I haven't found any SPs that do this, but only part of Meditech is SQL based (the DR part). So, I'm thinking that it would be better to pull this directly from Meditech, and reduce our risk of doing something unsupported in SharePoint.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply