how can i read it ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

how can i read it ?

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-07-31 : 06:29:32

i have a table that hols log of user data of the our viewer cube on of the type column is nvarchar like:
<SlicerData> <Hierarchies> <Hierarchy UName="[Dim_Customer].[Markets]"/> <Hierarchy UName="[Dim_Reps].[Reps Categories]"/>
<Hierarchy UName="[Dim_Product].[Product Groups]"/> <Hierarchy UName="[Dim_Customer].[Agents]"/> .......

Is simple whay to change this to table in sql?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-31 : 06:42:37

Sure.
How would you like the table to look like?

N 56°04'39.26"
E 12°55'05.63"

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-07-31 : 07:11:04

load the value without "tags" <SlicerData>,<Hierarchies>,

how do you recomended to laod it,and what way?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-31 : 08:34:45

Like this?

-- Sample data
DECLARE	@Sample TABLE
	(
		Data XML NOT NULL
	);

INSERT	@Sample
	(
		Data
	)
VALUES	('<SlicerData> <Hierarchies> <Hierarchy UName="[Dim_Customer].[Markets]"/> <Hierarchy UName="[Dim_Reps].[Reps Categories]"/><Hierarchy UName="[Dim_Product].[Product Groups]"/> <Hierarchy UName="[Dim_Customer].[Agents]"/> </Hierarchies></SlicerData> ');

-- Solution
SELECT		n.value('@UName', 'VARCHAR(200)') AS UName
FROM		@Sample
CROSS APPLY	Data.nodes('SlicerData/Hierarchies/Hierarchy') AS d(n)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources