在sql server中读取xml字符串

本文介绍了在sql server中读取xml字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

你好吗。

我需要在sqlserver中读取xml字符串。这是xmlfile

hi how are you.

i need to read xml string in sqlserver. this is xmlfile

declare @stringxml xml

set @stringxml =''
<NewDataSet>
  <Table1>
    <groupname>Superannuation Pension</groupname>
    <effectivedate>11/02/2012</effectivedate>
    <termdate>11/02/2012</termdate>
    <groupvalue>PE</groupvalue>
  </Table1>
  <Table1>
    <groupname>Family Pension</groupname>
    <effectivedate>11/02/2012</effectivedate>
    <termdate>11/02/2012</termdate>
    <groupvalue>PF</groupvalue>
  </Table1>
  <Table1>
    <groupname>Judges Pension</groupname>
    <effectivedate>11/02/2012</effectivedate>
    <termdate>11/02/2012</termdate>
    <groupvalue>PJ</groupvalue>
  </Table1>
  <Table2>
    <groupname>Pension Arrear</groupname>
    <effectivedate>11/02/2012</effectivedate>
    <termdate>11/02/2012</termdate>
    <groupvalue>tbllocationPJ</groupvalue>
  </Table2>
  <Table2>
    <groupname>First Pension</groupname>
    <effectivedate>11/02/2012</effectivedate>
    <termdate>11/02/2012</termdate>
    <groupvalue>tbllocationPE</groupvalue>
  </Table2>
  <Table2>
    <groupname>Leave Travel Concession</groupname>
    <effectivedate>11/02/2012</effectivedate>
    <termdate>11/02/2012</termdate>
    <groupvalue>tbllocationPF</groupvalue>
  </Table2>
</NewDataSet>''

我需要

Actuallly i need

<Table1>

xml和另一个临时表中的

xml in temporary table and

<table2>

xml。

请帮我解决这个问题

xml in another temporary table.

Please help me on this

推荐答案

declare @stringxml xml

set @stringxml =''
<NewDataSet>
  <Table1>
    <groupname>Superannuation Pension</groupname>
    <effectivedate>11/02/2012</effectivedate>
    <termdate>11/02/2012</termdate>
    <groupvalue>PE</groupvalue>
  </Table1>
  <Table1>
    <groupname>Family Pension</groupname>
    <effectivedate>11/02/2012</effectivedate>
    <termdate>11/02/2012</termdate>
    <groupvalue>PF</groupvalue>
  </Table1>
  <Table1>
    <groupname>Judges Pension</groupname>
    <effectivedate>11/02/2012</effectivedate>
    <termdate>11/02/2012</termdate>
    <groupvalue>PJ</groupvalue>
  </Table1>
  <Table2>
    <groupname>Pension Arrear</groupname>
    <effectivedate>11/02/2012</effectivedate>
    <termdate>11/02/2012</termdate>
    <groupvalue>tbllocationPJ</groupvalue>
  </Table2>
  <Table2>
    <groupname>First Pension</groupname>
    <effectivedate>11/02/2012</effectivedate>
    <termdate>11/02/2012</termdate>
    <groupvalue>tbllocationPE</groupvalue>
  </Table2>
  <Table2>
    <groupname>Leave Travel Concession</groupname>
    <effectivedate>11/02/2012</effectivedate>
    <termdate>11/02/2012</termdate>
    <groupvalue>tbllocationPF</groupvalue>
  </Table2>
</NewDataSet>''


Declare @intPointer int
EXEC sp_xml_preparedocument @intPointer OUTPUT, @stringxml

SELECT * into #Table1 FROM OpenXml(@intPointer,''/NewDataSet/Table1'',2)
WITH (groupname varchar(100), effectivedate datetime, termdate datetime, groupvalue varchar(100))

SELECT * FROM #Table1

EXEC sp_xml_removedocument @intPointer

DROP TABLE #Table1

这篇关于在sql server中读取xml字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

XML

在sql server中读取xml字符串

问题描述

推荐答案