您的位置: 首页  >  技术问答  >  嗨，我有一个Xml文件数据如何将其转换为Sql中的表

嗨，我有一个Xml文件数据如何将其转换为Sql中的表

分类: 技术问答 • 2022-05-23 21:25:49
问题描述：

<?xml version="1.0"?>

-<CalData>

<Serial/>


-<LefCalInfo>

<Uncapped>false</Uncapped>

<NoOfSessionCals>-1</NoOfSessionCals>

<NoOfUsageCals>-1</NoOfUsageCals>

<NoOfNamedCals>20</NoOfNamedCals>

<NoOfClusterNodes>1</NoOfClusterNodes>

<NoOfDocumentCals>80</NoOfDocumentCals>

<NoOfInfrequentNamedCals>0</NoOfInfrequentNamedCals>

<NoOfConcurrentInfrequentNamedCals>0</NoOfConcurrentInfrequentNamedCals>

</LefCalInfo>


-<NamedCalsAllocated>


-<CalAllocEntry>

<Name>COLOSERV1\AJAY</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E489C87F258BF2</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\ASHISH</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4958393E93E94</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\ASHOK</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E49849EDB363BF</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\CFOBRIDGE1</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4990E1CD2DE3F</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\CFOBRIDGE2</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E499091585E0E7</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\CFOBRIDGE3</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4990C69876543</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\CHANDRA</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4916E67104EE3</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\INDRANI</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E49849F240795D</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\JIGISHA.S</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E493CE6F98D76B</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\KPKADMIN</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E498B61BE02469</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\MAHESH</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4620C258BF259</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\PIYUSH</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E498CB1B97530F</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\PRASHANT.G</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4944647042BFE</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\RAHUL</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E489695CD2DE3F</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\RAJ</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E493315EA00C23</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\SEETHALAKSHMI.H</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4976FF839A5BC</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\SHYJU.R</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E49527F6FE1A8C</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\VIMAL.T</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E499085F92C5F9</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\VISHY.C</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4916E3D0F8CB4</LastUsed>

</CalAllocEntry>

</NamedCalsAllocated>


-<PerServerCalData>


-<PerServerCalData>

<ServerName>COLOSERV1</ServerName>

<LastUpdated>40E4990EC1E573AC</LastUpdated>

</PerServerCalData>

</PerServerCalData>

<AvailableUsageCals>BFF0000000000000</AvailableUsageCals>

<UsageCalUpdateTimestamp>0000000000000000</UsageCalUpdateTimestamp>


-<PerDocumentCalData>


-<PerDocumentCalData>

<DocumentName>IVFA\ACT\ACT.QVW</DocumentName>

<NoOfNamedCals>5</NoOfNamedCals>


-<NamedCalsAllocated>


-<CalAllocEntry>

<Name>COLOSERV1\CFOBRIDGE1</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E487AD6468ACF1</LastUsed>

</CalAllocEntry>

</NamedCalsAllocated>

<AllowDynamicAllocation>true</AllowDynamicAllocation>

<NoOfEmbeddedNamedCals>0</NoOfEmbeddedNamedCals>

<EmbeddedSerial/>

<NamedCalsInUse/>

</PerDocumentCalData>


-<PerDocumentCalData>

<DocumentName>IVFA\ACT\ACT_TALLY.QVW</DocumentName>

<NoOfNamedCals>2</NoOfNamedCals>


-<NamedCalsAllocated>


-<CalAllocEntry>

<Name>COLOSERV1\INDRANI</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E478F0E2833945</LastUsed>

</CalAllocEntry>

</NamedCalsAllocated>

<AllowDynamicAllocation>true</AllowDynamicAllocation>

<NoOfEmbeddedNamedCals>0</NoOfEmbeddedNamedCals>

<EmbeddedSerial/>

<NamedCalsInUse/>

</PerDocumentCalData>


-<PerDocumentCalData>

<DocumentName>IVFA\FMG\FMG.QVW</DocumentName>

<NoOfNamedCals>6</NoOfNamedCals>


-<NamedCalsAllocated>


-<CalAllocEntry>

<Name>COLOSERV1\CHASE.H</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E486E9E2FC9630</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\GEOFF.W</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E486E9ECF1357A</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\SANDEEP.K</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E46E32612F684C</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\SEBASTIAN.M</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E48B2F76E5D4C4</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\VAIBHAV.O</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4988B36543210</LastUsed>

</CalAllocEntry>

</NamedCalsAllocated>

<AllowDynamicAllocation>true</AllowDynamicAllocation>

<NoOfEmbeddedNamedCals>0</NoOfEmbeddedNamedCals>

<EmbeddedSerial/>

<NamedCalsInUse/>

</PerDocumentCalData>


-<PerDocumentCalData>

<DocumentName>IVFA\FMG\FMG_AUTOMATION.QVW</DocumentName>

<NoOfNamedCals>1</NoOfNamedCals>


-<NamedCalsAllocated>


-<CalAllocEntry>

<Name>COLOSERV1\VAIBHAV.O</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E48110AC5F92C6</LastUsed>

</CalAllocEntry>

</NamedCalsAllocated>

<AllowDynamicAllocation>true</AllowDynamicAllocation>

<NoOfEmbeddedNamedCals>0</NoOfEmbeddedNamedCals>

<EmbeddedSerial/>

<NamedCalsInUse/>

</PerDocumentCalData>


-<PerDocumentCalData>

<DocumentName>IVFA\FUND_MIS\IVFA_FUND.QVW</DocumentName>

<NoOfNamedCals>3</NoOfNamedCals>


-<NamedCalsAllocated>


-<CalAllocEntry>

<Name>COLOSERV1\AMITH</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>FE37E43C8800759C</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\DHARMESH</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>FE37E43C8800759C</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\SUTHESH</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>FE37E43C8800759C</LastUsed>

</CalAllocEntry>

</NamedCalsAllocated>

<AllowDynamicAllocation>false</AllowDynamicAllocation>

<NoOfEmbeddedNamedCals>0</NoOfEmbeddedNamedCals>

<EmbeddedSerial/>

<NamedCalsInUse/>

</PerDocumentCalData>


-<PerDocumentCalData>

<DocumentName>IVFA\FUND_MIS\MIS\IVFA_MIS.QVW</DocumentName>

<NoOfNamedCals>1</NoOfNamedCals>


-<NamedCalsAllocated>


-<CalAllocEntry>

<Name>COLOSERV1\DHARMESH</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>FE37E43C8800759C</LastUsed>

</CalAllocEntry>

</NamedCalsAllocated>

<AllowDynamicAllocation>false</AllowDynamicAllocation>

<NoOfEmbeddedNamedCals>0</NoOfEmbeddedNamedCals>

<EmbeddedSerial/>

<NamedCalsInUse/>

</PerDocumentCalData>


-<PerDocumentCalData>

<DocumentName>IVFA\HICARE\HICARE.QVW</DocumentName>

<NoOfNamedCals>10</NoOfNamedCals>


-<NamedCalsAllocated>


-<CalAllocEntry>

<Name>COLOSERV1\CFOBRIDGE3</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4978BA32D21C1</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\NAITIK</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E49890A93E93E9</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\RAHUL.P</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E498923F3DD1BB</LastUsed>

</CalAllocEntry>

</NamedCalsAllocated>

<AllowDynamicAllocation>true</AllowDynamicAllocation>

<NoOfEmbeddedNamedCals>0</NoOfEmbeddedNamedCals>

<EmbeddedSerial/>

<NamedCalsInUse/>

</PerDocumentCalData>


-<PerDocumentCalData>

<DocumentName>IVFA\IFAPL\IFAPL_WIP.QVW</DocumentName>

<NoOfNamedCals>5</NoOfNamedCals>


-<NamedCalsAllocated>


-<CalAllocEntry>

<Name>COLOSERV1\CFOBRIDGE3</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E497889CEB2408</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\FINCARE1</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4988D38091A2B</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\FINCARE3</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4958CD6480F2C</LastUsed>

</CalAllocEntry>


-<CalAllocEntry>

<Name>COLOSERV1\SUTHESH</Name>

<ToBeDeleted>FE37E43C8800759C</ToBeDeleted>

<LastUsed>40E4990B966054F4</LastUsed>

</CalAllocEntry>

</NamedCalsAllocated>

<AllowDynamicAllocation>true</AllowDynamicAllocation>

<NoOfEmbeddedNamedCals>0</NoOfEmbeddedNamedCals>

<EmbeddedSerial/>

<NamedCalsInUse/>

</PerDocumentCalData>


-<PerDocumentCalData>

<DocumentName>IVFA\INLOGISTICS\IN_LOGISTICS.QVW</DocumentName>

<NoOfNamedCals>3</NoOfNamedCals>

<NamedCalsAllocated/>

<AllowDynamicAllocation>true</AllowDynamicAllocation>

<NoOfEmbeddedNamedCals>0</NoOfEmbeddedNamedCals>

<EmbeddedSerial/>

<NamedCalsInUse/>

</PerDocumentCalData>


-<PerDocumentCalData>

<DocumentName>IVFA\INLOGISTICS\IN_LOG_OP_DASHBOARD.QVW</DocumentName>

<NoOfNamedCals>3</NoOfNamedCals>

<NamedCalsAllocated/>

<AllowDynamicAllocation>true</AllowDynamicAllocation>

<NoOfEmbeddedNamedCals>0</NoOfEmbeddedNamedCals>

<EmbeddedSerial/>

<NamedCalsInUse/>

</PerDocumentCalData>


-<PerDocumentCalData>

<DocumentName>IVFA\INTERNAL\FUND\IVFA_FUND.QVW</DocumentName>

<NoOfNamedCals>1</NoOfNamedCals>


-<NamedCalsAllocated>


-<CalAllocEntry<
答

Not sure if I understo od the question correctly but if you want to add the data from CalAllocEntry elements into a table using T-SQL then perhaps something like

Not sure if I understood the question correctly but if you want to add the data from CalAllocEntry elements into a table using T-SQL then perhaps something like
DECLARE @xmldoc xml
SET @xmldoc = '
<caldata>
   <serial />
   <lefcalinfo>
      <uncapped>false</uncapped>
      <noofsessioncals>-1</noofsessioncals>
      <noofusagecals>-1</noofusagecals>
      <noofnamedcals>20</noofnamedcals>
      <noofclusternodes>1</noofclusternodes>
      <noofdocumentcals>80</noofdocumentcals>
      <noofinfrequentnamedcals>0</noofinfrequentnamedcals>
      <noofconcurrentinfrequentnamedcals>0</noofconcurrentinfrequentnamedcals>
   </lefcalinfo>
   <namedcalsallocated>
      <calallocentry>
         <name>COLOSERV1\AJAY</name>
         <tobedeleted>FE37E43C8800759C</tobedeleted>
         <lastused>40E489C87F258BF2</lastused>
      </calallocentry>
      <calallocentry>
         <name>COLOSERV1\ASHISH</name>
         <tobedeleted>FE37E43C8800759C</tobedeleted>
         <lastused>40E4958393E93E94</lastused>
      </calallocentry>
      <calallocentry>
         <name>COLOSERV1\ASHOK</name>
         <tobedeleted>FE37E43C8800759C</tobedeleted>
         <lastused>40E49849EDB363BF</lastused>
      </calallocentry>
   </namedcalsallocated>
</caldata>'


SELECT a.col1.value('(Name)[1]', 'nvarchar(100)') AS Name,
       a.col1.value('(ToBeDeleted)[1]', 'nvarchar(100)') AS ToBeDeleted,
	   a.col1.value('(LastUsed)[1]', 'nvarchar(100)') AS LastUsed
INTO #SomeTable
FROM @xmldoc.nodes('/CalData/NamedCalsAllocated/CalAllocEntry') a(col1)

SELECT * FROM #SomeTable





The result would be



The result would be 

Name             ToBeDeleted	   LastUsed
--------------   ----------------  -----------------
COLOSERV1\AJAY   FE37E43C8800759C  40E489C87F258BF2
COLOSERV1\ASHISH FE37E43C8800759C  40E4958393E93E94
COLOSERV1\ASHOK  FE37E43C8800759C  40E49849EDB363BF


Hi



which tag need to create table in sql ?



check this link

http://vb.net-informations.com/xml/open-xml-vb.net.htm[^]



In Above link \"Product\" is one of the tag,using for loop u can read its value.



XML File Parsing in VB.NET[^]







Regards

Araindb
Hi

which tag need to create table in sql ?

check this link
http://vb.net-informations.com/xml/open-xml-vb.net.htm[^]

In Above link "Product" is one of the tag,using for loop u can read its value.

XML File Parsing in VB.NET[^]



Regards
Araindb


Well your question is too generic, similarly your xml is also complex and does not seem to be fit one single Table. But we can guide you to have an import mechanism. Check this Import xml into sql server[^] link
Well your question is too generic, similarly your xml is also complex and does not seem to be fit one single Table. But we can guide you to have an import mechanism. Check this Import xml into sql server[^] link


相关推荐