view creation using xml column on linked/distributed server

  • 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!

  • 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

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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