SQL XML select multiple attributes

Question

i just need your help. I was looking for a solution, but nothing works yet.

I have to select multiple attributes from the xml-file which is stored in a column of my table.

This is the File:

<ManagerConfig>
   <AccountList>
     <Account accountID=“1“  friendlyName=“Testname1“> Test </Account>
     <Account accountID=“2“  friendlyName=“Testname2“> Test </Account>
     <Account accountID=“3“  friendlyName=“Testname3“> Test </Account>
     <Account accountID=“4“  friendlyName=“Testname4“> Test </Account>
   </AccountList
</ManagerConfig>

For this I´m using the following statement:

set @accountID = @xmlxx.value('(/ManagerConfig/AccountList/Account/@accountId)[1]', 'varchar(max)')
set @friendlyName = @xmlxx.value('(/ManagerConfig/AccountList/Account/@friendlyName)[1]', 'varchar(max)')

The result is:

accountID     friendlyname
1             Testname1

When im changing the value from [1] to [2] im getting the second attribute. So thats no problem. But i need all of these attributes and export them into another temporary table. I thought i can replace the value with a variable [@i]:

set @accountID = @xmlxx.value('(/(ManagerConfig/AccountList/Account/@accountId)'[@i]'', 'varchar(max)')

But there is a syntax error:

An insufficient number of arguments were supplied for the procedure or function value.

I hope you can help me to find a solution..

Greetz Dennis

But you're trying to assign the *result* into a scalar variable. How do you expect to stuff 4 values into a variable that can contain 1 value? — Damien_The_Unbeliever, Jun 19 '12 at 13:02

score 2 · Answer 1 · answered Jun 19 '12 at 13:08

Assuming you want to transition to getting a result set (that can contain multiple results), rather than your current use of a variable, something like:

select t.C.value('@accountID','int') as AccountID,t.C.value('@friendlyName','varchar(max)') as FriendlyName
from @xmlxx.nodes('/ManagerConfig/AccountList/Account') as t(C)

(Original setup and test script, cleaning from odd formatting in question, and correcting Id -> ID, which may be the wrong direction for the fix):

declare @xmlxx xml = '<ManagerConfig>
   <AccountList>
     <Account accountID="1"  friendlyName="Testname1"> Test </Account>
     <Account accountID="2"  friendlyName="Testname2"> Test </Account>
     <Account accountID="3"  friendlyName="Testname3"> Test </Account>
     <Account accountID="4"  friendlyName="Testname4"> Test </Account>
   </AccountList>
</ManagerConfig>'
declare @accountID varchar(max)
declare @friendlyName varchar(max)
set @accountID = @xmlxx.value('(/ManagerConfig/AccountList/Account/@accountID)[1]', 'varchar(max)')
set @friendlyName = @xmlxx.value('(/ManagerConfig/AccountList/Account/@friendlyName)[1]', 'varchar(max)')

thank you for the fast answer. Can you tell me how to define a variable which contains this resultset: select t.C.value('@accountID','int') as AccountID, t.C.value('@friendlyName','varchar(max)') as FriendlyName — user1463983, Jun 19 '12 at 14:02
@user1463983 - you can create a [table variable](http://msdn.microsoft.com/en-us/library/ms174335) - see example "B. Inserting data into a table variable" (unfortunately, there are *multiple* Example B's on that page) — Damien_The_Unbeliever, Jun 19 '12 at 14:29

SQL XML select multiple attributes

1 Answers1