XML column data extract(T-SQL) - Need help

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 2012 Forums

Transact-SQL (2012)

XML column data extract(T-SQL) - Need help

Author

Topic

manjunathvenkatesh
Starting Member

2 Posts

Posted - 2014-08-21 : 12:41:10

I am trying to extract the below highlighted data from an XML column value (column - Details) as shown in the example below,

Work Table structure (T SQL)
Identity Details
1 <ab Line="32" section="Furniture" />
2 <ab Line="37" section="Household" />
3 <ab Line="38" section="Electronics" />

Result should be
Id Details
32 Furniture
37 Household
38 Electronics

I have tried ,

select
X.N.value('Line[1]', 'int') as Id
,X.N.value('section[1]', 'varchar(5)') as Details
from Work as T
cross apply T.Details.nodes('/ab') as X(N)

select EventParameter.value('(ab/Line)[1]','int') as Id
,EventParameter.value('(ab/section)[1]','varchar(10)') as Details
from Work

SELECT
R.ref.value ('@Line', 'int') as Id
FROM Work
CROSS APPLY EventParameter.nodes ('/ab/Line') R(ref)

All of these above queries are returning NULL in result set.

Could you please advise me on how to extract column value from this XML column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-21 : 16:27:50

[code]DECLARE @Sample TABLE
(
RowID INT PRIMARY KEY CLUSTERED,
Data XML NOT NULL
);

INSERT @Sample
(
RowID,
Data
)
VALUES (1, '<ab Line="32" section="Furniture" />'),
(2, '<ab Line="37" section="Household" />'),
(3, '<ab Line="38" section="Electronics" />');

-- SwePeso
SELECT ab.n.value('(@Line)', 'INT') AS Line,
ab.n.value('(@section)', 'VARCHAR(100)') AS Section
FROM @Sample AS s
CROSS APPLY s.Data.nodes('(/ab)') AS ab(n);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

manjunathvenkatesh
Starting Member

2 Posts

Posted - 2014-08-22 : 03:48:02

Thank You.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources