在sql server中读取xml字符串 [英] read xml string in sql server

问题描述

你好吗。



我需要在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

推荐答案

试试吧，让我们来吧我知道你有没有查询:)

Try this, let me know if you have any query :)

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字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！