Looping and XML

• cmayeux

  Old Hand

  Points: 323

  More actions

  November 2, 2010 at 7:59 am

  #225746

  I've attached two text files. One containing XML that resides in a field in a SQL table, the other some TSQL code that attempts to "read" some of that information and display it as records in a temporary table.

  I would like to modify the SQL so that I have a loop that gathers the data for display.

  I've tried breaking up the XQuery (Polygon[1]/Parts[1]/Point[1]/Coordinate[1]/X[1]) portion into variables, and concatenating them together to produce the final statement, but that doesn't seem to be the correct way to do what I want.

  I just want to be able to vary the Point index value from 1 to n, n being the Parts Count (in the XML), in this case 4.

  I've tried to find examples both in SQL Server help and in various postings, but can't seem to get it right.

  Can anyone help me with this syntax?

• WayneS

  SSC Guru

  Points: 95460

  More actions

  November 2, 2010 at 8:34 am

  #1245428

  Does this help you out?

  declare @test-2 TABLE (RowID int IDENTITY PRIMARY KEY CLUSTERED,

  XMLData XML);

  INSERT INTO @test-2

  Values ('<Polygon>

  <PolygonId>8343f355-a26b-4db7-adc0-b2715a9ea2f9</PolygonId>

  <Parts Count="4">

  <Point>

  <PointId>b905ea74-55de-42ee-8373-42fa5ca6e5e7</PointId>

  <Coordinate>

  <X>-92.831803081</X>

  <Y>27.587246425</Y>

  <CrsCode>LL27</CrsCode>

  <CrsType>Geodetic</CrsType>

  </Coordinate>

  <Z>0</Z>

  <ZReference />

  <VerticalDatum />

  <HasZComponent>false</HasZComponent>

  <AcquisitionMethod />

  <AcquisitionMethodComment />

  <ZAcquisitionMethod />

  <ZAcquisitionMethodComment />

  <AcquisitionSource />

  <VersionProvider />

  <AcquisitionCrsDetails />

  <EditCrsDetails />

  <CreatedDate>0001-01-01T00:00:00</CreatedDate>

  <CreatedUser />

  <AttributeSet Count="0" />

  </Point>

  <Point>

  <PointId>f4082784-53b9-47a5-a861-f31ac80f714d</PointId>

  <Coordinate>

  <X>-92.782885297</X>

  <Y>27.587178553</Y>

  <CrsCode>LL27</CrsCode>

  <CrsType>Geodetic</CrsType>

  </Coordinate>

  <Z>0</Z>

  <ZReference />

  <VerticalDatum />

  <HasZComponent>false</HasZComponent>

  <AcquisitionMethod />

  <AcquisitionMethodComment />

  <ZAcquisitionMethod />

  <ZAcquisitionMethodComment />

  <AcquisitionSource />

  <VersionProvider />

  <AcquisitionCrsDetails />

  <EditCrsDetails />

  <CreatedDate>0001-01-01T00:00:00</CreatedDate>

  <CreatedUser />

  <AttributeSet Count="0" />

  </Point>

  <Point>

  <PointId>9e9edabe-9fe4-4d38-9c6f-f2e0d0309a3f</PointId>

  <Coordinate>

  <X>-92.782971044</X>

  <Y>27.543590211</Y>

  <CrsCode>LL27</CrsCode>

  <CrsType>Geodetic</CrsType>

  </Coordinate>

  <Z>0</Z>

  <ZReference />

  <VerticalDatum />

  <HasZComponent>false</HasZComponent>

  <AcquisitionMethod />

  <AcquisitionMethodComment />

  <ZAcquisitionMethod />

  <ZAcquisitionMethodComment />

  <AcquisitionSource />

  <VersionProvider />

  <AcquisitionCrsDetails />

  <EditCrsDetails />

  <CreatedDate>0001-01-01T00:00:00</CreatedDate>

  <CreatedUser />

  <AttributeSet Count="0" />

  </Point>

  <Point>

  <PointId>0c756865-8666-4e28-837f-7b73d066485c</PointId>

  <Coordinate>

  <X>-92.831869508</X>

  <Y>27.543657961</Y>

  <CrsCode>LL27</CrsCode>

  <CrsType>Geodetic</CrsType>

  </Coordinate>

  <Z>0</Z>

  <ZReference />

  <VerticalDatum />

  <HasZComponent>false</HasZComponent>

  <AcquisitionMethod />

  <AcquisitionMethodComment />

  <ZAcquisitionMethod />

  <ZAcquisitionMethodComment />

  <AcquisitionSource />

  <VersionProvider />

  <AcquisitionCrsDetails />

  <EditCrsDetails />

  <CreatedDate>0001-01-01T00:00:00</CreatedDate>

  <CreatedUser />

  <AttributeSet Count="0" />

  </Point>

  </Parts>

  <Holes Count="0" />

  </Polygon>');

  select t.RowID,

  PointID = x.data.value('PointId[1]','uniqueidentifier'),

  CordX = x.data.value('Coordinate[1]/X[1]','float'),

  CordY = x.data.value('Coordinate[1]/Y[1]','float'),

  HasZComp= x.data.value('HasZComponent[1]','bit')

  from @test-2 t

  CROSS APPLY XMLData.nodes('/Polygon/Parts/Point') AS x(data)

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• cmayeux

  Old Hand

  Points: 323

  More actions

  November 2, 2010 at 8:44 am

  #1245442

  Well, can I do this without putting the XML into a table with a key?

  Since the XML is in a variable, @theXML, can I get the detail out of it in one statement?

• WayneS

  SSC Guru

  Points: 95460

  More actions

  November 2, 2010 at 9:16 am

  #1245479

  Assuming @theXML is of datatype XML:

  declare @theXML XML;

  set @theXML = '<Polygon>

  <PolygonId>8343f355-a26b-4db7-adc0-b2715a9ea2f9</PolygonId>

  <Parts Count="4">

  <Point>

  <PointId>b905ea74-55de-42ee-8373-42fa5ca6e5e7</PointId>

  <Coordinate>

  <X>-92.831803081</X>

  <Y>27.587246425</Y>

  <CrsCode>LL27</CrsCode>

  <CrsType>Geodetic</CrsType>

  </Coordinate>

  <Z>0</Z>

  <ZReference />

  <VerticalDatum />

  <HasZComponent>false</HasZComponent>

  <AcquisitionMethod />

  <AcquisitionMethodComment />

  <ZAcquisitionMethod />

  <ZAcquisitionMethodComment />

  <AcquisitionSource />

  <VersionProvider />

  <AcquisitionCrsDetails />

  <EditCrsDetails />

  <CreatedDate>0001-01-01T00:00:00</CreatedDate>

  <CreatedUser />

  <AttributeSet Count="0" />

  </Point>

  <Point>

  <PointId>f4082784-53b9-47a5-a861-f31ac80f714d</PointId>

  <Coordinate>

  <X>-92.782885297</X>

  <Y>27.587178553</Y>

  <CrsCode>LL27</CrsCode>

  <CrsType>Geodetic</CrsType>

  </Coordinate>

  <Z>0</Z>

  <ZReference />

  <VerticalDatum />

  <HasZComponent>false</HasZComponent>

  <AcquisitionMethod />

  <AcquisitionMethodComment />

  <ZAcquisitionMethod />

  <ZAcquisitionMethodComment />

  <AcquisitionSource />

  <VersionProvider />

  <AcquisitionCrsDetails />

  <EditCrsDetails />

  <CreatedDate>0001-01-01T00:00:00</CreatedDate>

  <CreatedUser />

  <AttributeSet Count="0" />

  </Point>

  <Point>

  <PointId>9e9edabe-9fe4-4d38-9c6f-f2e0d0309a3f</PointId>

  <Coordinate>

  <X>-92.782971044</X>

  <Y>27.543590211</Y>

  <CrsCode>LL27</CrsCode>

  <CrsType>Geodetic</CrsType>

  </Coordinate>

  <Z>0</Z>

  <ZReference />

  <VerticalDatum />

  <HasZComponent>false</HasZComponent>

  <AcquisitionMethod />

  <AcquisitionMethodComment />

  <ZAcquisitionMethod />

  <ZAcquisitionMethodComment />

  <AcquisitionSource />

  <VersionProvider />

  <AcquisitionCrsDetails />

  <EditCrsDetails />

  <CreatedDate>0001-01-01T00:00:00</CreatedDate>

  <CreatedUser />

  <AttributeSet Count="0" />

  </Point>

  <Point>

  <PointId>0c756865-8666-4e28-837f-7b73d066485c</PointId>

  <Coordinate>

  <X>-92.831869508</X>

  <Y>27.543657961</Y>

  <CrsCode>LL27</CrsCode>

  <CrsType>Geodetic</CrsType>

  </Coordinate>

  <Z>0</Z>

  <ZReference />

  <VerticalDatum />

  <HasZComponent>false</HasZComponent>

  <AcquisitionMethod />

  <AcquisitionMethodComment />

  <ZAcquisitionMethod />

  <ZAcquisitionMethodComment />

  <AcquisitionSource />

  <VersionProvider />

  <AcquisitionCrsDetails />

  <EditCrsDetails />

  <CreatedDate>0001-01-01T00:00:00</CreatedDate>

  <CreatedUser />

  <AttributeSet Count="0" />

  </Point>

  </Parts>

  <Holes Count="0" />

  </Polygon>';

  select PointID = x.data.value('PointId[1]','uniqueidentifier'),

  CordX = x.data.value('Coordinate[1]/X[1]','float'),

  CordY = x.data.value('Coordinate[1]/Y[1]','float'),

  HasZComp= x.data.value('HasZComponent[1]','bit')

  from @theXML.nodes('/Polygon/Parts/Point') AS x(data);

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• cmayeux

  Old Hand

  Points: 323

  More actions

  November 2, 2010 at 9:43 am

  #1245502

  That's exactly what I was looking for!

  If I wanted to include a sequence number (1,2,3,,,) for each point based on the order within the XML, how would I do that?

• WayneS

  SSC Guru

  Points: 95460

  More actions

  November 2, 2010 at 10:55 am

  #1245547

  cmayeux (11/2/2010)

  ---

  That's exactly what I was looking for!

  If I wanted to include a sequence number (1,2,3,,,) for each point based on the order within the XML, how would I do that?

  MS SQL doesn't support the position() function yet. There are some workarounds here; while you're there, vote on it.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2