MS SQL Server Clients - OpenXML - Selecting Nodes by Index

You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

Author

OpenXML - Selecting Nodes by Index

Bryan

2005-12-15, 8:24 pm

Is it possible to select a node (such as the AKANAME node below) by index to
flatten the data and insert it into a field? A simple version of my dilemma
can be found below.

Many thanks in advance!

/* XML
<DOCUMENT>
<FULLNAME></FULLNAME>
<AKANAME></AKANAME>
<AKANAME></AKANAME>
<AKANAME></AKANAME>
</DOCUMENT>
*/
DECLARE @doc int
exec sp_xml_preparedocume
nt @doc OUTPUT, @Data

INSERT dbo.myTable (
FullName,
Other_AKAName, Other_AKAName2, Other_AKAName3
SELECT XML_Name, XML_Other_AKAName,
XML_Other_Other_AKAN
ame2, XML_Other_AKAName3
FROM OPENXML(@doc, '/DOCUMENT')
WITH (
XML_Other_Name VARCHAR(35) 'FULLNAME',
XML_Other_AKAName VARCHAR(35) 'AKANAME', --FIRST NODE
XML_Other_AKAName2 VARCHAR(35) 'AKANAME', --SECOND NODE
XML_Other_AKAName3 VARCHAR(35) 'AKANAME' --THIRD NODE
)

EXEC sp_xml_removedocumen
t @doc

Sponsored Links