原贴:http://topic.csdn.net/u/20100412/14/f6941844-e9bf-4ed7-a0c2-bef03d775783.html?70615
declare @data xml=
'<root>
<SaleMan>
<SaleManId>1</SaleManId>
<SaleManCode>001</SaleManCode>
<Nodes>
<Node>
<NodeId>101</NodeId>
<NodeCode>101</NodeCode>
<CardSetting>
<R1>1</R1>
<R2>2</R2>
<R3>3</R3>
</CardSetting>
</Node>
<Node>
<NodeId>102</NodeId>
<NodeCode>102</NodeCode>
<CardSetting>
<R1>1</R1>
<R2>2</R2>
<R3>3</R3>
<R8>0</R8>
</CardSetting>
</Node>
<Node>
<NodeId>103</NodeId>
<NodeCode>103</NodeCode>
<CardSetting>
<R1>1</R1>
<R2>2</R2>
<r3>3</r3>
</CardSetting>
</Node>
</Nodes>
</SaleMan>
<SaleMan>
<SaleManId>2</SaleManId>
<SaleManCode>002</SaleManCode>
<Nodes>
<Node>
<NodeId>101</NodeId>
<NodeCode>101</NodeCode>
<CardSetting>
<R1>5</R1>
<R2>7</R2>
<R3>4</R3>
<R8>3</R8>
</CardSetting>
</Node>
</Nodes>
</SaleMan>
<SaleMan>
<SaleManId>3</SaleManId>
<SaleManCode>003</SaleManCode>
<Nodes>
<Node>
<NodeId>101</NodeId>
<NodeCode>101</NodeCode>
<CardSetting>
<R1>4</R1>
<R2>5</R2>
<R3>6</R3>
</CardSetting>
</Node>
</Nodes>
</SaleMan>
</root>'
declare @dh int;
exec sp_xml_preparedocument @dh output, @data;
select * from openxml(@dh,'//SaleMan/Nodes/Node') with
(SaleManId int '../../SaleManId', SaleManCode char(3) '../../SaleManCode',
NodeId int './NodeId', NodeCode char(3) './NodeCode',
R1 int './CardSetting/R1', R2 int './CardSetting/R2',
R3 int './CardSetting/R3', R8 int './CardSetting/R8');
exec sp_xml_removedocument @dh;
/*
1 001 101 101 1 2 3 NULL
1 001 102 102 1 2 3 0
1 001 103 103 1 2 NULL NULL
2 002 101 101 5 7 4 3
3 003 101 101 4 5 6 NULL
*/