XML取值应用实例

原贴：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
*/