creaxiahaisse
New Member
I'm trying to load XML data into a SQL Server table.The XML file is:\[code\]<root> <Company ID="183"> <User UserName="Kim" /> <User UserName = "Joe" /> </Company> <Company ID="123"> <User UserName="George" /> <User UserName = "Sal" /> </Company></root>\[/code\]I would like to get a table of UserName that has both the company ID and the UserName.for this example:\[code\]ID UserName--- --------183 Kim183 Joe123 George123 Sal\[/code\]I've tried a few things, such as looping through all usernames of each Company, but this is quite complex especially when I have more than 2 layers to suit for this solution.Another thing I tried is:\[code\]SELECTt.c.query('./UserName').value('.','varchar(20)') AS A ,( SELECT t1.c1.value('@ID', 'varchar(10)') FROM @x.nodes('/root/Company')AS t1(c1))FROM @x.nodes('/root/Company/User')AS t(c)\[/code\]but I get an error that the subquery has more than 1 value.Any suggestions?Thanks!