Traverse in XML file

• manutosh pandey

  SSC-Addicted

  Points: 488

  More actions

  December 4, 2007 at 3:34 am

  #201522

  Hi All,

  I am using the openxml function sql server 2000.but in this I am giving the hard coded stuff as I found in tutorial like '\root\order'.

  But how can i traverse xml file that we have passed as run time to traverse the how many level of nesting and etc.

  e.g. /root/customer/order so i'll give /root/* and I want to traverse in customer and order via meta properties.Any helpful example will vanish my doubt.Thanks in advance.

  Thanks and Regards,

  Manutosh

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  December 4, 2007 at 4:31 am

  #756846

  manutosh pandey (12/4/2007)

  ---

  Hi All,

  I am using the openxml function sql server 2000.but in this I am giving the hard coded stuff as I found in tutorial like '\root\order'.

  But how can i traverse xml file that we have passed as run time to traverse the how many level of nesting and etc.

  e.g. /root/customer/order so i'll give /root/* and I want to traverse in customer and order via meta properties.Any helpful example will vanish my doubt.Thanks in advance.

  Thanks and Regards,

  Manutosh

  Hi Manutosh,

  could you give us an example (you can paste xml into the post, just make sure you replace < with &lt;

  I.e. the xml nodes you are looking for, are they just at a different level (in which case you can use '//customer' or are they structurally different (called for e.g. "somecustomer" in one case, "client" in the other)

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• manutosh pandey

  SSC-Addicted

  Points: 488

  More actions

  December 4, 2007 at 5:06 am

  #756866

  This kind of xml I have to traverse dynamically.

  Thanks and Regards,

  Manutosh Pandey

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  December 4, 2007 at 8:20 am

  #756970

  Looks like your xml did not make it. Please replace all < with &lt;. In Management Studio or Visual Studio: Ctr-H, ...

  You can check if the XML displays properly in the post by previewing it, or you may as well attach it as a text file.

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• manutosh pandey

  SSC-Addicted

  Points: 488

  More actions

  December 5, 2007 at 2:39 am

  #757265

  <root>

  <Department>

  <Department_ID>10</Department_ID>

  <Department_Name>Government </Department_Name>

  <Employees>

  <Employee>

  <Employee_Name>Manutosh</Employee_Name>

  <Employee_Age>28</Employee_Age>

  </Employee>

  </Employees>

  </Department>

  <Department>

  <Department_ID>20</Department_ID>

  <Department_Name>ECMP </Department_Name>

  <Employees>

  <Employee>

  <Employee_Name>Virender</Employee_Name>

  <Employee_Age>27</Employee_Age>

  </Employee>

  </Employees>

  </Department>

  <Department>

  <Department_ID>30</Department_ID>

  <Department_Name>FS </Department_Name>

  <Employees>

  <Employee>

  <Employee_Name>Sumit</Employee_Name>

  <Employee_Age>24</Employee_Age>

  </Employee>

  </Employees>

  </Department>

  </root>

  this is my xml file Andras and that can be come in some other format also means a more level of nesting so how can I traverse in runtime xml file.

  Thanks and Regards,

  Manutosh Pandey

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  December 5, 2007 at 7:24 am

  #757324

  Hi Manutosh,

  this time the XML made it :). I'm wondering if you have an example of the "other format" as well. IF ti is only the nestedness of employees, you can get all the employees via:

  OPENXML(@docHandle, N'//Employee')

  E.g.:

  DECLARE @docHandle INT

  DECLARE @xmlDocument XML

  SET @xmlDocument = N'<root>

  <Department>

  <Department_ID>10</Department_ID>

  <Department_Name>Government </Department_Name>

  <Employees>

  <Employee>

  <Employee_Name>Manutosh</Employee_Name>

  <Employee_Age>28</Employee_Age>

  </Employee>

  </Employees>

  </Department>

  <Department>

  <Department_ID>20</Department_ID>

  <Department_Name>ECMP </Department_Name>

  <Employees>

  <Employee>

  <Employee_Name>Virender</Employee_Name>

  <Employee_Age>27</Employee_Age>

  </Employee>

  </Employees>

  </Department>

  <Department>

  <Department_ID>30</Department_ID>

  <Department_Name>FS </Department_Name>

  <Employees>

  <Employee>

  <Employee_Name>Sumit</Employee_Name>

  <Employee_Age>24</Employee_Age>

  </Employee>

  </Employees>

  </Department>

  </root>'

  EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

  SELECT *

  FROM OPENXML(@docHandle, N'//Employee')

  EXEC sp_xml_removedocument @docHandle

  The above will give you quite a lot of extra information about what kind of elements there are in the Employee node, the ids of teh various nodes, as well as the parent-child relationships.

  You could use xquery too, for example:

  SELECT T.Emp.query('.'), T.Emp.value('(.//Employee_Age)[1]', 'int')

  FROM @xmldocument.nodes('//Employee') AS T ( Emp )

  The above will get the age of an employee even if the age is further nested.

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software