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

XML Column Read Expand / Collapse
Author
Message
Posted Monday, February 4, 2013 8:23 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 13, 2015 2:17 AM
Points: 110, Visits: 376
Hi,
I have a column in table with xml type. I want to extract it row wise but it is giving me only first result

My Table has following data
id ItemId
1 <itemids><itemid>259</itemid><itemid>489</itemid></itemids>
2 <itemids><itemid>6834262</itemid><itemid>489</itemid></itemids>
3 <itemids><itemid>6603537</itemid></itemids>

select
tabId,
itemid.value('(/itemids//itemid/node())[1]','int') AS ItemIDA
from mytable;

query is only returning first ItemId against every Id.
Post #1415340
Posted Monday, February 4, 2013 8:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 26, 2015 7:10 AM
Points: 2,468, Visits: 8,053
Based on your sample data: -
IF object_id('tempdb..#yourTable') IS NOT NULL
BEGIN
DROP TABLE #yourTable;
END;

SELECT id, CAST(ItemId AS XML) AS ItemId
INTO #yourTable
FROM (VALUES(1, '<itemids><itemid>259</itemid><itemid>489</itemid></itemids>'),
(2, '<itemids><itemid>6834262</itemid><itemid>489</itemid></itemids>'),
(3, '<itemids><itemid>6603537</itemid></itemids>')
)a(id, ItemId);

You'd do it like this: -
SELECT id, ParamValues.ItemId.value('.','INT') AS ItemId
FROM #yourTable
OUTER APPLY ItemId.nodes('/itemids/itemid') ParamValues(ItemId);




Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Post #1415348
Posted Monday, February 4, 2013 11:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 13, 2015 2:17 AM
Points: 110, Visits: 376
thank you. :)
this is what I have implemented
Post #1415417
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse