How to sort out Xml errors

Question

when i am trying to execute my xml code i am getting a error message like this

XQuery [value()]: Top-level attribute nodes are not supported

and the code is 

DECLARE @ProgramMembersXml xml
SELECT @ProgramMembersXml = '<ProgramMembers><ProgramMember ProgramID="112" Member="Family"/><ProgramMember ProgramID="110" Member="Family"/><ProgramMember ProgramID="71" Member="Family"/><ProgramMember ProgramID="72" Member="Family"/><ProgramMember ProgramID="73" Member="Family"/><ProgramMember ProgramID="98" Member="Family"/><ProgramMember ProgramID="99" Member="Family"/><ProgramMember ProgramID="100" Member="Family"/><ProgramMember ProgramID="67" Member="Family"/><ProgramMember ProgramID="111" Member="Family"/><ProgramMember ProgramID="75" Member="Client"/><ProgramMember ProgramID="103" Member="Client"/><ProgramMember ProgramID="108" Member="Client"/><ProgramMember ProgramID="91" Member="Client"/><ProgramMember ProgramID="77" Member="Client"/><ProgramMember ProgramID="93" Member="Client"/><ProgramMember ProgramID="94" Member="Client"/><ProgramMember ProgramID="95" Member="Client"/><ProgramMember ProgramID="96" Member="Client"/><ProgramMember ProgramID="97" Member="Client"/><ProgramMember ProgramID="87" Member="Family"/><ProgramMember ProgramID="104" Member="Client"/><ProgramMember ProgramID="109" Member="Client"/><ProgramMember ProgramID="42" Member="Client"/><ProgramMember ProgramID="65" Member="Client"/><ProgramMember ProgramID="66" Member="Client"/><ProgramMember ProgramID="68" Member="Client"/><ProgramMember ProgramID="69" Member="Client"/><ProgramMember ProgramID="70" Member="Client"/><ProgramMember ProgramID="101" Member="Client"/><ProgramMember ProgramID="81" Member="Family"/><ProgramMember ProgramID="102" Member="Family"/><ProgramMember ProgramID="83" Member="Family"/><ProgramMember ProgramID="84" Member="Client"/><ProgramMember ProgramID="92" Member="Client"/><ProgramMember ProgramID="105" Member="Family"/><ProgramMember ProgramID="106" Member="Family"/><ProgramMember ProgramID="107" Member="Family"/><ProgramMember ProgramID="85" Member="Family"/><ProgramMember ProgramID="78" Member="Family"/><ProgramMember ProgramID="79" Member="Family"/><ProgramMember ProgramID="80" Member="Family"/></ProgramMembers>'


DECLARE @TempProgramMembers TABLE(ProgramID int, Members varchar(50))
INSERT INTO @TempProgramMembers 
SELECT @ProgramMembersXml.value('@ProgramID', 'int')
,@ProgramMembersXml.value('@Member', 'varchar(50)')
FROM @ProgramMembersXml.nodes('/ProgramMembers/ProgramMember')AS T(ProgramMembers) 




DECLARE @ProgramProvidersXml xml
SELECT @ProgramProvidersXml = '<ProgramProviders><ProgramProvider ProgramID="108" ProviderID="1880"/><ProgramProvider ProgramID="109" ProviderID="1884"/><ProgramProvider ProgramID="110" ProviderID="1885"/><ProgramProvider ProgramID="111" ProviderID="1892"/><ProgramProvider ProgramID="112" ProviderID="1885"/><ProgramProvider ProgramID="74" ProviderID="1885"/><ProgramProvider ProgramID="71" ProviderID="1885"/><ProgramProvider ProgramID="72" ProviderID="1885"/><ProgramProvider ProgramID="73" ProviderID="1885"/><ProgramProvider ProgramID="98" ProviderID="1885"/><ProgramProvider ProgramID="99" ProviderID="1885"/><ProgramProvider ProgramID="100" ProviderID="1885"/><ProgramProvider ProgramID="67" ProviderID="1885"/><ProgramProvider ProgramID="75" ProviderID="1893"/><ProgramProvider ProgramID="91" ProviderID="1880"/><ProgramProvider ProgramID="77" ProviderID="1880"/><ProgramProvider ProgramID="93" ProviderID="1880"/><ProgramProvider ProgramID="94" ProviderID="1880"/><ProgramProvider ProgramID="95" ProviderID="1880"/><ProgramProvider ProgramID="96" ProviderID="1880"/><ProgramProvider ProgramID="97" ProviderID="1880"/><ProgramProvider ProgramID="87" ProviderID="1891"/><ProgramProvider ProgramID="42" ProviderID="1884"/><ProgramProvider ProgramID="65" ProviderID="1884"/><ProgramProvider ProgramID="66" ProviderID="1884"/><ProgramProvider ProgramID="68" ProviderID="1884"/><ProgramProvider ProgramID="69" ProviderID="1884"/><ProgramProvider ProgramID="70" ProviderID="1884"/><ProgramProvider ProgramID="101" ProviderID="1884"/><ProgramProvider ProgramID="81" ProviderID="1896"/><ProgramProvider ProgramID="102" ProviderID="1896"/><ProgramProvider ProgramID="82" ProviderID="1895"/><ProgramProvider ProgramID="83" ProviderID="1889"/><ProgramProvider ProgramID="84" ProviderID="1881"/><ProgramProvider ProgramID="92" ProviderID="1881"/><ProgramProvider ProgramID="85" ProviderID="1886"/><ProgramProvider ProgramID="76" ProviderID="1886"/><ProgramProvider ProgramID="78" ProviderID="1886"/><ProgramProvider ProgramID="79" ProviderID="1886"/><ProgramProvider ProgramID="80" ProviderID="1886"/><ProgramProvider ProgramID="86" ProviderID="1887"/><ProgramProvider ProgramID="103" ProviderID="1880"/><ProgramProvider ProgramID="104" ProviderID="1884"/><ProgramProvider ProgramID="105" ProviderID="1886"/><ProgramProvider ProgramID="106" ProviderID="1886"/><ProgramProvider ProgramID="107" ProviderID="1886"/></ProgramProviders>'


DECLARE @TempProgramProviders TABLE(ProgramID int, ProviderID int)
INSERT INTO @TempProgramProviders
SELECT @ProgramProvidersXml.value('@ProgramID', 'int')
,@ProgramProvidersXml.value('@ProviderID', 'int')
FROM @ProgramProvidersXml.nodes('/ProgramProviders/ProgramProvider')AS T(ProgramMembers) 

can any one help me out...

Answer 1

Could be XML file structure problem

DECLARE @x XML


     SET @x = '
     <ProgramProviders>
        <ProgramProvider ProgramID="108" ProviderID="1880"> </ProgramProvider>
        <ProgramProvider ProgramID="110" ProviderID="1885"></ProgramProvider>
     </ProgramProviders>'


 SELECT 
       x.item.value('@ProgramID[1]', 'VARCHAR(20)') AS ProgramID,
       x.item.value('@ProviderID[1]', 'VARCHAR(20)') AS ProviderID
  FROM @x.nodes('//ProgramProviders/ProgramProvider') AS x(item)

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Hi Dasari,

So the error is just saying that Attributes cannot be at the top, only Elements can be at the top (because Attributes are basically like scalar parameter values). To fix this you need syntax like ("ElementName/@AttributeName").

Please refer to this thread to solve your problem: http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/871099bf-532a-4eab-b8ed-f96d701fe6da/

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Thanks for your reply but we didn't get the required output and again we are facing to many errors in our code.

Could you please give me any alternative regarding this error.

waiting for your reply.

Answer 4

You may get those errors because of not properly built  XML document

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/