How can I get a list of columns from this SQL Server XML field?
I have an xml fragment like the following example and I'd like to get the list of columns out and turn them into a comma-separated list but I'm really struggling to f开发者_运维知识库igure out the syntax for working with the XML data type and the documentation I've found reads like Korean stereo instructions :(
<ROOT>
<DATAVIEW>sp_Demo</DATAVIEW>
<WHERECLAUSE>X=4</WHERECLAUSE>
<COLUMNS>
<COLUMN>ImageHTML</COLUMN>
<COLUMN>Task_ID</COLUMN>
</COLUMNS>
</ROOT>
Can anyone point me at a beginners example or give me a pointer on how to do this?
From the XML fragment above I'd like to return a string containing "ImageHTML,Task_ID"
Well it's not pretty but this does the trick, thanks for the pointer Louis.
DECLARE MY_CURSOR Cursor
FOR
Select Substring(Substring(CAST(T.Item.query('.') AS varchar(50)),0,LEN(CAST(T.Item.query('.') AS varchar(50)))-8),9,LEN(CAST(T.Item.query('.') AS varchar(50))))
From @Data.nodes('ROOT/COLUMNS/COLUMN') AS T(Item)
Open MY_CURSOR
DECLARE @ColumnName varchar(50)
DECLARE @RunningTotal varchar(2000)
SET @RunningTotal=''
FETCH NEXT FROM MY_CURSOR INTO @ColumnName
WHILE (@@Fetch_STATUS<>-1)
BEGIN
IF (@@FETCH_STATUS<>-2)
IF LEN(@RunningTotal)>0 SET @RunningTotal=@RunningTotal+','
SET @RunningTotal=@RunningTotal+@ColumnName
FETCH NEXT FROM MY_CURSOR INTO @ColumnName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
PRINT @RunningTotal
Final Update: While my cursor version worked, Akash presented a much neater solution below.
You do not need a cursor here. You need two things:
- the xml data type methods
.nodes()
and.value()
- a well-known trick for producing a comma-separated list from a rowset
Setup:
DECLARE @xml xml
SET @xml = '
<ROOT>
<DATAVIEW>sp_Demo</DATAVIEW>
<WHERECLAUSE>X=4</WHERECLAUSE>
<COLUMNS>
<COLUMN>ImageHTML</COLUMN>
<COLUMN>Task_ID</COLUMN>
</COLUMNS>
</ROOT>
'
Output:
DECLARE @output nvarchar(max)
SELECT
@output = COALESCE(@output + ', ', '') + C.value('text()[1]', 'nvarchar(MAX)')
FROM
@xml.nodes('//COLUMN') X(C)
SELECT @output
Explanation:
.nodes()
performs a shred of xml into rows. In this case, it interrogates the xml with the xpath //COLUMN
and produces a rowset with each matching node as a row. This rowset is aliased to a table named X
with one column C
.
In the SELECT
clause, each xml node has its text()
value extracted with .value()
The @output
trick is a standard method for producing a comma-delimited list.
Output:
ImageHTML, Task_ID
Hopefully these terms and methods will enable you to find the apropriate docs and examples.
Is this of some help ?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61409
enjoy
精彩评论