Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Extracting and parsing XML from fields (CRM related) Expand / Collapse
Author
Message
Posted Sunday, January 8, 2012 2:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
LutzM (1/7/2012)
You'll need the DelimitedSplit8K function to split the data. If you don't have it in your tool box already, search this site for it (originally posted by Jeff Moden).


Lutz,

Thank you for the reference but you might want to update the "Delimited Split Function" link in your signature to the newer, more performant version at the following link...
http://www.sqlservercentral.com/articles/Tally+Table/72993/


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1232144
Posted Sunday, January 8, 2012 2:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 7,165, Visits: 13,261
Jeff Moden (1/8/2012)
LutzM (1/7/2012)
You'll need the DelimitedSplit8K function to split the data. If you don't have it in your tool box already, search this site for it (originally posted by Jeff Moden).


Lutz,

Thank you for the reference but you might want to update the "Delimited Split Function" link in your signature to the newer, more performant version at the following link...
http://www.sqlservercentral.com/articles/Tally+Table/72993/


Done as requested
Thank you for the updated link and the reminder to change my signature!




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1232147
Posted Sunday, January 8, 2012 3:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
My pleasure. It's an honor that you're carrying some of my articles as a link in your signature. Thanks, Lutz.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1232150
Posted Monday, January 9, 2012 4:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:55 AM
Points: 24, Visits: 89
I've taken the suggested code and played around with it to get a better understaning of how it works. The fruits of my playing are below:

WITH TempTable AS (
SELECT ConstraintBasedGroupId, Constraints
FROM ConstraintBasedGroupBase
)
SELECT
TempTable.ConstraintBasedGroupId,
SUBSTRING(item,CHARINDEX('{',item)+1,CHARINDEX('}',item)-CHARINDEX('{',item)-1) AS TeamId
FROM TempTable
CROSS APPLY
DelimitedSplit8K(REPLACE(Constraints,'||','|'),'|') split
) AS ResourceGroupToTeam

This is fantastic, in that it does what I want it to (it newly introduces me to CROSS APPLY and removes the complexity of messing around with the XML). However, I've fallen foul of a different (and I suspect very simple) problem at the next hurdle.

The above code returns the IDs in two fields. My first though was to use the above as a derrive table with the other two tables involed to get the text, something like this:

SELECT  ResourceGroupBase.Name AS ResourceGroupName,
TeamBase.Name AS TeamName
FROM ResourceGroupBase
INNER JOIN
(
WITH TempTable AS (
SELECT ConstraintBasedGroupId, Constraints
FROM ConstraintBasedGroupBase
)
SELECT
TempTable.ConstraintBasedGroupId,
SUBSTRING(item,CHARINDEX('{',item)+1,CHARINDEX('}',item)-CHARINDEX('{',item)-1) AS TeamId
FROM TempTable
CROSS APPLY
DelimitedSplit8K(REPLACE(Constraints,'||','|'),'|') split
) AS ResourceGroupToTeam
ON ResourceGroupBase.ResourceGroupId = ResourceGroupToTeam.ConstraintBasedGroupId
INNER JOIN
TeamBase
ON ResourceGroupToTeam.TeamId = TeamBase.TeamId

I've never worked with 'WITH' before, so I'm sure it is has restrictions on its use that I'm falling foul of. Given that this code block will form the basis of other queries (and so it would be nice if it could be discretely inserted as a unit), how should I write the code above to make the final query (ResourceGroupName and TeamName) work?

Thanks for the responses so far.

Stuart
Post #1232309
Posted Monday, January 9, 2012 4:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:45 AM
Points: 1,694, Visits: 19,552

Common table expressions (the 'WITH' bit) go at the start of the query.

WITH TempTable AS (
SELECT ConstraintBasedGroupId, Constraints
FROM ConstraintBasedGroupBase
),
ResourceGroupToTeam AS (
SELECT
TempTable.ConstraintBasedGroupId,
SUBSTRING(item,CHARINDEX('{',item)+1,CHARINDEX('}',item)-CHARINDEX('{',item)-1) AS TeamId
FROM TempTable
CROSS APPLY
DelimitedSplit8K(REPLACE(Constraints,'||','|'),'|') split
)
SELECT ResourceGroupBase.Name AS ResourceGroupName,
TeamBase.Name AS TeamName
FROM ResourceGroupBase
INNER JOIN ResourceGroupToTeam
ON ResourceGroupBase.ResourceGroupId = ResourceGroupToTeam.ConstraintBasedGroupId
INNER JOIN TeamBase
ON ResourceGroupToTeam.TeamId = TeamBase.TeamId



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1232314
Posted Monday, January 9, 2012 6:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:55 AM
Points: 24, Visits: 89
That works!

Thanks to all involved.

Stuart
Post #1232376
Posted Thursday, September 19, 2013 1:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 4:57 PM
Points: 1, Visits: 4
I have run into similar issues regarding this table and extracting information out of the constraints column. I attempted to run your code samples but they do not run. Do you happen to have a full code sample that you can supply?

Basically I just want to extract the ID's out of that constraints column for a specific row on the ConstraintBasedGroupBase table and put them into another table so I can select against them. Why in the world they store the resource Id's like this is beyond me!
Post #1496574
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse