I have a stored procedure which passes a reference to an XML document
(SQL 2000 style) using an int variable @idoc to another proc.
The receiving proc extracts the elements to grab the appropriate
fields values. The document looks like this:
<Account>
<AccountInfo AccountDestination="Alpha AccountSource="myDatabase"
AccountStatus="Active" />
</Account>
Currently, the proc uses this select to grab the data.
SELECT @AccountDestination = AccountDestination,
@AccountSource = AccountSource,
@AccountStatus = AccountStatus
FROM OPENXML(@idoc, '/Account/AccountInfo', 1) WITH (
@AccountDestination varchar(30),
@AccountSource varchar(50),
@AccountStatus varchar(30)
)
The XML document will be changing though such that a new element
(NewElement) is added along with some child nodes
(NewElementChildNode).
<Account>
<AccountInfo AccountDestination="Alpha AccountSource="myDatabase"
AccountStatus="Active" />
<NewElement>
<NewElementChildNode Name="testName" Address="testAddress" /
>
<NewElementChildNode Name="anotherTestName"
Address="anotherTestAddress" />
</NewElement>
</Account>
Now, there will be the NewElement, under which I need to be able to
grab the data from each NewElementChildNode.
How can I do this using a similar SELECT with OPENXML? Do I need to
cursor through however many NewElementChildNode elements and process
each child node individually? Again I'm stuck with SQL 2000
conventions here so I'm not able to use the new XML data type provided
in 2005, for example.
Thanks in advance for suggestions.
>> Stay informed about: XML document referenced as an idoc?