动态表创建基于XML的文档类型定义 [英] Dynamic Table Creation based on Document Type Definition of XML
本文介绍了动态表创建基于XML的文档类型定义的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
早安技术人员，
可以在任何一个共享您的专业知识。
May any one share your expertise .
我有一个XML文档（ 1GB大小）与文档类型定义。
i have an XML document (1GB Size) with a Document Type Definition.
我需要的数据加载到SQL表，但面临的挑战是要在运行时创建的表结构取决于.dtd文件。
i need to load the data to sql table, but the challenge is the table structure must be created during run time depends upon the .dtd file.
.dtd文件是不断根据客户的客户不断变化的。
The .dtd file is keep on changing based on customer to customer
我新到XML，请帮帮我。我可以提供XML和.dtd如果需要的。
i am new to the xml, Please help me. i can provide xml and .dtd if its required.
代码我已经习惯了从XML提取数据表
;With Cte
AS
(SELECT i.value('(../@action)[1]','varchar(20)') AppAction,
i.value('(../@id)[1]','varchar(20)') AppId,
i.value('.','varchar(20)') Notes,
i.value('(../BaseVehicle/@id)[1]','varchar(20)') BaseVehicle,
k.value('(./Qual/@id)[1]','varchar(20)') Qual,
i.value('(../Qty)[1]','varchar(20)') Qty,
i.value('(../PartType/@id)[1]','varchar(20)') PartType,
i.value('(../SubModel/@id)[1]','varchar(20)') SubModel,
i.value('(../EngineBase/@id)[1]','varchar(20)') EngineBase,
i.value('(../EngineVIN/@id)[1]','varchar(20)') EngineVIN,
k.value('(./MfrLabel)[1]','varchar(20)') MfrLabel,
i.value('(../Position/@id)[1]','varchar(20)') PositionId,
i.value('(../Part)[1]','varchar(20)') Part,
k.value('(./Qual/param/@value)[1]','varchar(20)') ParamValue,
j.value('.','varchar(20)') RecordCount
FROM @X.nodes('AMUL/App[@action="A"]/Note') x(i)
OUTER APPLY x.i.nodes('../../Footer/RecordCount')y(j)
OUTER APPLY y.j.nodes('../../App')z(k))
SELECT
AppAction,AppId,BaseVehicle,Qual,Qty,PartType,MfrLabel,PositionId,Part,ParamValue,SubModel,EngineBase,EngineVIN,RecordCount,
STUFF((SELECT ' ; ' + Notes
FROM Cte X WHERE X.BaseVehicle = Y.BaseVehicle
GROUP BY BaseVehicle,Notes
FOR XML PATH('')), 1, 2, '') Note
FROM Cte Y
GROUP BY
BaseVehicle,AppAction,AppId,Qual,Qty,PartType,MfrLabel,PositionId,Part,ParamValue,SubModel,EngineBase,EngineVIN,RecordCount
由于一吨。
推荐答案
这是第一次尝试：
这将创建（实际上只是申报）四大表（文件，应用程序，QUAL和票据）
你的数据被插入与创建外键。
的最后行是一个完全接合结果集
口包括所有的XML节点到目标表以确保，该遗漏的信息也不会丢失...
It will create (actually just declare) four tables (file, app, qual and note) Your data is inserted with created foreign keys. The last lines are a fully joined resultset I included all XML-Nodes into the target tables to make sure, that missed information is not lost...
只是整件事复制到空查询窗口中，执行和探险的最终结果，这是否符合您的需要与否。
Just copy the whole thing into an empty query window, execute and explorer the final result whether this fits to your needs or not.
如果是，请不要别忘了投票和接受。这花了大量的努力： - ）
If yes, please don't forget to vote up and accept. This took quite some effort :-)
编码愉快！
DECLARE @x xml='<?xml version="1.0" encoding="utf-8"?>
<ACES version="3.0">
<Header>
<Company>Godrej</Company>
<SenderName>chail</SenderName>
<SenderPhone>xxx-712-xxxx</SenderPhone>
<TransferDate>2009-09-30</TransferDate>
<BrandAAIAID>tbzx</BrandAAIAID>
<DocumentTitle>Godrej Data</DocumentTitle>
<EffectiveDate>2009-09-30</EffectiveDate>
<SubmissionType>FULL</SubmissionType>
<xcdbVersionDate>2008-08-28</xcdbVersionDate>
<reltersionDate>2008-06-10</reltersionDate>
<xcbdVersionDate>2020-09-25</xcbdVersionDate>
</Header>
<App action="A" id="1">
<BaseVehicle id="2555"/>
<Qual id="15231">
<text>Light Duty Brakes</text>
</Qual>
<Qty>2</Qty>
<PartType id="10054"/>
<MfrLabel>Professional Grade</MfrLabel>
<Position id="22"/>
<Part>816-4000</Part>
</App>
<App action="A" id="3">
<BaseVehicle id="2557"/>
<Qual id="15231">
<text>Light Duty Brakes</text>
</Qual>
<Qty>2</Qty>
<PartType id="10054"/>
<MfrLabel>Professional Grade</MfrLabel>
<Position id="22"/>
<Part>816-4000</Part>
</App>
<App action="A" id="908">
<BaseVehicle id="1513"/>
<Qual id="9174">
<text>Standard Nut</text>
</Qual>
<Qual id="21521">
<text>Requires Stamped Retainer And Cotter Pin</text>
</Qual>
<Qty>2</Qty>
<PartType id="10054"/>
<MfrLabel>Professional Grade</MfrLabel>
<Position id="22"/>
<Part>816-4018</Part>
</App>
<App action="A" id="4007">
<BaseVehicle id="7947"/>
<Qual id="9743">
<param value="8/91"/>
<text>Thru 8/91</text>
</Qual>
<Qty>2</Qty>
<PartType id="10014"/>
<MfrLabel>Professional Grade</MfrLabel>
<Position id="22"/>
<Part>816-4265</Part>
</App>
<App action="A" id="1">
<BaseVehicle id="5861" />
<Note>Manual</Note>
<Qty>1</Qty>
<PartType id="13117" />
<Position id="12" />
<Part>955-304</Part>
</App>
<App action="A" id="9951">
<BaseVehicle id="3883" />
<Note>Reflector</Note>
<Note>Packaging Type: Box</Note>
<Qty>1</Qty>
<PartType id="11720" />
<Position id="88" />
<Part>1650140</Part>
</App>
<App action="A" id="16578">
<BaseVehicle id="18667" />
<SubModel id="694" />
<Note>Composite Type</Note>
<Note>Smoked Lens, w/Black Trim</Note>
<Qty>1</Qty>
<PartType id="10762" />
<Position id="12" />
<Part>1591142</Part>
</App>
<App action="A" id="19633">
<BaseVehicle id="18659" />
<Note>Power</Note>
<Note>w/Heat</Note>
<Note>wo/Memory</Note>
<Qty>1</Qty>
<PartType id="13117" />
<Position id="12" />
<Part>955-1162</Part>
</App>
<App action="A" id="83948">
<BaseVehicle id="4470" />
<Note>Compare to Original</Note>
<Note>Bulb Call-Out Size: 194</Note>
<Note>Wattage: 1.2</Note>
<Note>Light Color: White</Note>
<Qty>1</Qty>
<PartType id="11730" />
<Position id="1" />
<Part>194W-SMD</Part>
</App>
<App action="A" id="2">
<BaseVehicle id="4935" />
<Note>M14 x 1.50 Thread</Note>
<Note>Package Quantity: 5</Note>
<Note>Dorman - AutoGrade - Boxed</Note>
<Note>Packaging Type: Box</Note>
<Qty>1</Qty>
<PartType id="5560" />
<Position id="1" />
<Part>090-053</Part>
</App>
<App action="A" id="288250">
<BaseVehicle id="16865" />
<SubModel id="20" />
<EngineBase id="5461" />
<EngineVIN id="40" />
<Note>Wheel Lock Type: Female Spline</Note>
<Note>Package Quantity: 4</Note>
<Note>Packaging Type: Card</Note>
<Note>Thread Size: 1/2-20</Note>
<Note>Chrome</Note>
<Note>Overall Length (In): 1.425</Note>
<Note>Steel</Note>
<Note>Thread Handling: Right Hand Thread</Note>
<Qty>1</Qty>
<PartType id="16214" />
<Position id="1" />
<Part>711-221</Part>
</App>
<Footer>
<RecordCount>1772522</RecordCount>
</Footer>
</ACES>';
DECLARE @tblFile TABLE(FileID INT
,ACES_Version VARCHAR(10)
,Header_Company VARCHAR(100)
,Header_SenderName VARCHAR(100)
--further Header-fields here
,Footer_RecordCount INT
,HeaderNode XML
,FooterNode XML);
INSERT INTO @tblFile
SELECT 1 AS FileID --Add something senseful here
,@x.value('/ACES[1]/@version','varchar(max)') AS ACES_Version
,@x.value('(/ACES/Header/Company)[1]','varchar(max)') AS Header_Company
,@x.value('(/ACES/Header/SenderName)[1]','varchar(max)') AS Header_SenderName
--further fields of header here
,@x.value('(/ACES/Footer/RecordCount)[1]','int') AS ACES_RecordCount
,@x.query('/ACES/Header') AS HeaderNode
,@x.query('/ACES/Footer') AS FooterNode;
DECLARE @tblApp TABLE(AppID INT
,FileID INT
,Action VARCHAR(10)
,ID INT
,BaseVehicleID INT
,Qty INT
--further App-fields here
,QualNodes XML
,NoteNodes XML
,AppNode XML);
INSERT INTO @tblApp
SELECT ROW_NUMBER() OVER(ORDER BY One.App.value('@id','int')) AS AppID
,1 AS FileID --Add something senseful here
,One.App.value('@action','varchar(10)') AS Action
,One.App.value('@id','int') AS ID
,One.App.value('BaseVehicle[1]/@id','int') AS BaseVehicleID
,One.App.value('Qty[1]','int') AS Qty
--further App-fields here
,'<Quals>' + CAST(One.App.query('Qual') AS VARCHAR(MAX)) + '</Quals>' AS QualNodes
,'<Notes>' + CAST(One.App.query('Note') AS VARCHAR(MAX)) + '</Notes>' AS NoteNodes
,One.App.query('.') AS AppNode
FROM @x.nodes('/ACES/App') AS One(App);
DECLARE @tblQual TABLE(AppID INT
,FileID INT
,QualID INT
,ID INT
,Text VARCHAR(150)
,ParamValue VARCHAR(150)
,QualNode XML);
INSERT INTO @tblQual
SELECT AppID
,FileID
,ROW_NUMBER() OVER(PARTITION BY AppID ORDER BY One.Qual.value('@id','int')) AS QualID
,One.Qual.value('@id','int') AS ID
,One.Qual.value('text[1]','varchar(max)') AS Text
,One.Qual.value('param[1]/@value','varchar(max)') AS ParamValue
,One.Qual.query('.') AS QualNode
FROM @tblApp AS tblApp
CROSS APPLY tblApp.QualNodes.nodes('/Quals/Qual') One(Qual)
DECLARE @tblNotes TABLE(AppID INT
,FileID INT
,NoteID INT
,ID INT
,NoteText VARCHAR(150)
,NoteNode XML);
INSERT INTO @tblNotes
SELECT AppID
,FileID
,ROW_NUMBER() OVER(PARTITION BY AppID ORDER BY (SELECT NULL)) AS NoteID
,One.Note.value('@id','int') AS ID
,One.Note.value('.','varchar(max)') AS NoteText
,One.Note.query('.') AS NoteNode
FROM @tblApp AS tblApp
CROSS APPLY tblApp.NoteNodes.nodes('/Notes/Note') One(Note)
SELECT *
FROM @tblFile AS f
INNER JOIN @tblApp AS a ON f.FileID=a.FileID
LEFT JOIN @tblQual AS q ON q.AppID=a.AppID
LEFT JOIN @tblNotes AS n ON n.AppID=a.AppID
这篇关于动态表创建基于XML的文档类型定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
