When querying XML in SQL Server 2005, multiple tags of the document are in the same row?

If you want to select out multiple "rows" from a XML document, you need to use the nodes() SQL Server XML function - something like this: SELECT @XMLTable. Value('(/Grower_Run/Run_ID)1', 'varchar(50)') AS 'RunID', Section. Value('(Grower_Run_Section/SectionID)1', 'varchar(50)') as 'SectionID' FROM @XMLTable.

Nodes('/Grower_Run/Crucible/Section') AS Tmp(Section) That XPath statement in the FROM clause basically defines a "pseudo-table" of XML elements - based on that XPath. So here you get a pseudo-table for each Section entry in your XML - from which you can then select individual elements using the value() function If you want to select that from a table that contains a column of type XML you might need to check into the CROSS APPLY command: SELECT t. SomeColumn, t.XmlColumn.

Value('(/Grower_Run/Run_ID)1', 'varchar(50)') AS 'RunID', Section. Value('(Grower_Run_Section/SectionID)1', 'varchar(50)') as 'SectionID' FROM dbo. YourTable t CROSS APPLY t.XmlColumn.

Nodes('/Grower_Run/Crucible/Section') AS Tmp(Section).

If you want to select out multiple "rows" from a XML document, you need to use the .nodes() SQL Server XML function - something like this: SELECT @XMLTable. Value('(/Grower_Run/Run_ID)1', 'varchar(50)') AS 'RunID', Section. Value('(Grower_Run_Section/SectionID)1', 'varchar(50)') as 'SectionID' FROM @XMLTable.

Nodes('/Grower_Run/Crucible/Section') AS Tmp(Section) That XPath statement in the FROM clause basically defines a "pseudo-table" of XML elements - based on that XPath. So here you get a pseudo-table for each entry in your XML - from which you can then select individual elements using the .value() function. If you want to select that from a table that contains a column of type XML, you might need to check into the CROSS APPLY command: SELECT t.

SomeColumn, t.XmlColumn. Value('(/Grower_Run/Run_ID)1', 'varchar(50)') AS 'RunID', Section. Value('(Grower_Run_Section/SectionID)1', 'varchar(50)') as 'SectionID' FROM dbo.

YourTable t CROSS APPLY t.XmlColumn. Nodes('/Grower_Run/Crucible/Section') AS Tmp(Section).

I see the idea of what your suggesting here but I'm having trouble implementing it. Trying to apply the top suggestion I'm getting an error: Must declare the scalar variable "@XML_TABLE". I'm loading my xml into a column of a table variable and then running these queryies off of that it it matters.

– Neberu Mar 9 at 20:14 @Neberu: the first approach would be valid is you had your XML in a SQL variable. If you have a table, you need to use the second approach – marc_s Mar 9 at 20:16 1 @neberu: okay, so which nodes would you like to enumerate? There IS only a single /Grower_Run/Crucible/Section in your sample XML - so you get only a single row as a result - works as designed... (but probably not as you intended it to work..() – marc_s Mar 9 at 20:53 1 Ding, you just helped me get it.

I just had move Grower_Run_Section down to the pseudo table declaration since it's that node that I need to enumerate. – Neberu Mar 9 at 21:17 1 @marc_s: +1 By the way. You know your SQL-Server!

– user357812 Mar 10 at 12:20.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.