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
Go to Top of Page

manjunathvenkatesh
Starting Member

2 Posts

Posted - 2014-08-22 : 03:48:02
Thank You.
Go to Top of Page
   

- Advertisement -