DECLARE @x XML
SET @x =
'
Dollars
0
$
T
F
1
1
Hours
0
H
F
T
2
0
EQP
0
ep
F
T
3
0
'
DECLARE @max INT, @i INT,
@maxStructures INT, @iStructure INT,
@maxColumns INT, @iColumn INT
SELECT @max = @x.query(' { count(/Units/Unit) } ').value('e[1]','int')
SELECT @maxStructures = @x.query(' { count(/Structures/Structure) } ').value('e[1]','int')
SELECT @maxColumns = @x.query(' { count(/Columns/Column) } ').value('e[1]','int')
SET @i = 1
SET @iStructure = 1
SET @iColumn = 1
DECLARE @UnitID VARCHAR(10),@UnitName VARCHAR(10), @Scale VARCHAR(10), @Symbol VARCHAR(10), @Prefix VARCHAR(10), @Pad VARCHAR(10), @UnitSeq VARCHAR(10), @RptSeq VARCHAR(10)
-- loop starts
WHILE @i <= @max BEGIN
-- select "Name" to the variable
SELECT
@UnitId = x.value('(//Unit/@id)[1]','VARCHAR(32)'),
@UnitName = x.value('(UnitName)[1]','VARCHAR(32)') ,
@Scale = x.value('(Scale)[1]','VARCHAR(32)') ,
@Symbol = x.value('(Symbol)[1]','VARCHAR(32)') ,
@Prefix = x.value('(Prefix)[1]','VARCHAR(32)') ,
@Pad = x.value('(Pad)[1]','VARCHAR(32)') ,
@UnitSeq = x.value('(UnitSeq)[1]','VARCHAR(32)') ,
@RptSeq = x.value('(RptSeq)[1]','VARCHAR(32)')
FROM
@x.nodes('/Units/Unit[position()=sql:variable("@i")]')
e(x)
SELECT @UnitID as 'Unit id', @UnitName as 'UnitName', @Scale as 'Scale', @Symbol as 'Symbol', @Prefix as 'Prefix', @Pad as 'Pad', @UnitSeq as 'UnitSeq', @RptSeq as 'RptSeq'
SET @i = @i + 1
END
indent preformatted text by 4 spaces
When this runs I get
un1 Dollars -----
un1 Hours etc
it should be un2 Hours