Using SQL WITH clause then merging it with SELECT statement in one procedure-CodePudding

I have a table with XML column of type nvarchar(max) ContactInformationXML. I managed to parse this column to get the data I want CountryMarket using this query:

WITH CastToXML AS (
    SELECT ContactInformationXML, TRY_CAST(ContactInformationXML AS xml) AS x
        FROM Archive_ProgUsers
        WHERE ContactInformationXML IS NOT NULL AND ContactInformationXML <> '' AND ContactInformationXML <> 'Admin - Old Program in Use' AND ContactInformationXML <> 'Admin - Admin Edits'
)
SELECT h.p.value('(/user/contact/@CountryMarket)[1]','nvarchar(max)') AS CountryMarket
    FROM CastToXML
        cross apply x.nodes('/user/contact[1]') AS h(p)

However, now that I have that column, I need to combine it with another query in the same procedure to display one table. Here is the second query:

SELECT DISTINCT
    user_id,
    email,
    app_id,
    ContactInformationXML,
    install_code,
    programStarts
FROM Archive_ProgUsers
INNER JOIN Archive_Installations ON Archive_ProgUsers.id = Archive_Installations.user_id
INNER JOIN (
    SELECT DISTINCT inst_id 
        , COUNT(CASE WHEN Archive_Hits.type <> 'New' AND Archive_Hits.accessed_on BETWEEN CAST(@startdate AS DATE) AND CAST(@enddate AS DATE) THEN 1 END) AS programStarts
    FROM Archive_Hits
    GROUP BY Archive_Hits.inst_id 
) AS hitsCount ON hitsCount.inst_id = Archive_Installations.id
WHERE programStarts > 0
ORDER BY programStarts DESC

I can't use UNION or UNION ALL because I must have an equal number of expressions in each list.

How can I merge those 2 queries into one procedure that returns one result table that looks like this??

Update Sample ContactInformationXML data:

<user Userid="John Smith"> <contact FirstName="John" LastName="Smith" RegistrationEmailAddress="[email protected]" DisplayEmailAddress="[email protected]" Company="Google" Title="" PhoneNum=" 6666666666" FaxNum="" Address1="example USA, 77" Address2="" City="Earth" StateName="testing;" StateCode="USA" ZipCode="00000" CountryCode="US" CountryMarket="North America" DateModified="2020-03-16T16:35:00"/> </user>

CodePudding user response：

Without knowing how the contact information XML is structured it's a bit hard to guess what the result should be, but based on your queries, you can do something like this:

With CastToXML AS (
    SELECT user_id, ContactInformationXML, TRY_CAST(ContactInformationXML AS xml) AS x
        FROM Archive_ProgUsers
        WHERE ContactInformationXML IS NOT NULL AND ContactInformationXML <> '' AND ContactInformationXML <> 'Admin - Old Program in Use' AND ContactInformationXML <> 'Admin - Admin Edits'
),
Market AS (
SELECT user_id, h.p.value('(/user/contact/@CountryMarket)[1]','nvarchar(max)') AS CountryMarket
    FROM CastToXML
        cross apply x.nodes('/user/contact[1]') AS h(p)
)
SELECT DISTINCT
    user_id,
    email,
    app_id,
    --ContactInformationXML,
    install_code,
    programStarts,
    CountryMarket
FROM Archive_ProgUsers
INNER JOIN Archive_Installations ON Archive_ProgUsers.id = Archive_Installations.user_id
INNER JOIN (
    SELECT DISTINCT inst_id 
        , COUNT(CASE WHEN Archive_Hits.type <> 'New' AND Archive_Hits.accessed_on BETWEEN CAST(@startdate AS DATE) AND CAST(@enddate AS DATE) THEN 1 END) AS programStarts
    FROM Archive_Hits
    GROUP BY Archive_Hits.inst_id 
) AS hitsCount ON hitsCount.inst_id = Archive_Installations.id
LEFT JOIN Market
  ON Market.user_id = Archive_ProgUsers.user_id
WHERE programStarts > 0
ORDER BY programStarts DESC

CodePudding user response：

It looks like you just have a single value you want to pull out of the XML. So you can do this very simply, by placing TRY_CAST in a CROSS APPLY and using .values on it.

You also don't need the WHERE because TRY_CAST will just return null in those cases

SELECT
    user_id,
    email,
    app_id,
    ContactInformationXML = v.x.value('(/user/contact/@CountryMarket)[1]','nvarchar(max)'),
    install_code,
    programStarts
FROM Archive_ProgUsers
INNER JOIN Archive_Installations ON Archive_ProgUsers.id = Archive_Installations.user_id
INNER JOIN (
    SELECT inst_id 
        , COUNT(CASE WHEN Archive_Hits.type <> 'New' AND Archive_Hits.accessed_on BETWEEN CAST(@startdate AS DATE) AND CAST(@enddate AS DATE) THEN 1 END) AS programStarts
    FROM Archive_Hits
    GROUP BY Archive_Hits.inst_id 
) AS hitsCount ON hitsCount.inst_id = Archive_Installations.id
CROSS APPLY (VALUES (TRY_CAST(ContactInformationXML AS xml)) ) v(x)
WHERE programStarts > 0
ORDER BY programStarts DESC

I'd advise you to rethink those DISTINCTs. They are probably unnecessary. If you are getting duplicates, you should look carefully at your joins. Don't just throw DISTINCT at it to make duplicates go away.