SQL xml转table

--插入数据

INSERT INTO @Tmp (ID,Resulr) VALUES (1,‘<root><row><ID>1</ID><score>80</score></row><row><ID>2</ID><score>85</score></row></root>‘)
INSERT INTO @Tmp (ID,Resulr) VALUES (2,‘<root><row><ID>1</ID><score>90</score></row><row><ID>2</ID><score>75</score></row></root>‘)

--原结果集

SELECT * FROM @Tmp AS t

<root>
  <row>
    <ID>1</ID>
    <score>80</score>
  </row>
  <row>
    <ID>2</ID>
    <score>85</score>
  </row>
</root>

--查询结果

SELECT t.ID,a.c1.value(‘ID[1]‘,‘int‘) AS xID
    ,a.c1.value(‘score[1]‘,‘int‘) AS score
FROM @Tmp AS t CROSS APPLY t.Resulr.nodes(‘root/row‘) a (c1)

SQL xml转table

标签：创建   images   root   style   --   插入数据   alt   apply   font