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 Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:26 AM
Points: 78, Visits: 252
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, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
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);




Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1415348
Posted Monday, February 4, 2013 11:15 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:26 AM
Points: 78, Visits: 252
thank you. :)
this is what I have implemented
Post #1415417
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse