Как импортировать XML в SQL Server при повторении базовых узлов
Я импортирую сгенерированные файлы XML в базу данных SQL Server. Он работал нормально, но теперь мне нужна дополнительная информация от повторяющегося узла, и я не могу понять это. Я использую VBScript с SQLXMLBulkLoad.SQLXMLBulkload.4.0 импортировать файлы. Здесь очень много <file> узлы в каждом файле, а здесь упрощенно в качестве примера:
<?xml version="1.0" encoding="UTF-8"?>
<samp_catalog version='1.0'>
<file>
<name>123.wav</name>
<xyz>
<a_samp>
<a_one>1</a_one>
<a_two>2</a_two>
<a_three>3</a_three>
</a_samp>
<b_samp version='2.0'>
<b_four>sample</b_four>
<b_five>sample</b_five>
<b_six>sample</b_six>
</b_samp>
<c_samp version='1.0'>
<c_entry>
<total_size>0</total_size>
<sig>sample</sig>
<type>SAMPLE</type>
<data_size>256</data_size>
<data encoding='string'></data>
</c_entry>
</c_samp>
<c_samp version='1.0'>
<c_entry>
<total_size>0</total_size>
<sig>sample</sig>
<type>PHONE</type>
<data_size>256</data_size>
<data encoding='string'>15555551212</data>
</c_entry>
</c_samp>
<c_samp version='1.0'>
<c_entry>
<total_size>0</total_size>
<sig>sample</sig>
<type>OTHER_SAMPLE</type>
<data_size>256</data_size>
<data encoding='string'></data>
</c_entry>
</c_samp>
</xyz>
</file>
</samp_catalog>
Без повторения c_samp узел, схема была простой:
<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema" xmlns:sql = "urn:schemas-microsoft-com:mapping-schema">
<xsd:element name = "samp_catalog" sql:is-constant = "1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name = "file" sql:relation = "files" maxOccurs = "unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name = "name" type = "xsd:string" sql:field = "name" />
<xsd:element name = "xyz" sql:is-constant = "1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name = "a_samp" sql:is-constant = "1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name = "a_one" type = "xsd:integer" sql:field = "a_one" />
<xsd:element name = "a_two" type = "xsd:integer" sql:field = "a_two" />
<xsd:element name = "a_three" type = "xsd:integer" sql:field = "a_three" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name = "b_samp" sql:is-constant = "1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name = "b_four" type = "xsd:string" sql:field = "b_four" />
<xsd:element name = "b_five" type = "xsd:string" sql:field = "b_five" />
<xsd:element name = "b_six" type = "xsd:string" sql:field = "b_six" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Важным является второй data элемент (из type ТЕЛЕФОН), но их импорт также приемлем. Я попытался определить все 3 c_sampS в схеме и определения sql:field только для указанного data элемент и определение его для всех элементов, однако импорт всегда приводит к NULL за все под c_samp элемент.
Как я могу получить повторяющиеся узлы для импорта в базу данных?
2 ответа
До сих пор я не нашел ответа на то, что я искал, но вот обходной путь, которым я сейчас пользуюсь. В VBScript после загрузки файла с Microsoft.XMLDOM Я перебираю файл и нахожу все type узлы, текстовое значение которых PHONE а затем создать новый элемент и добавить его к b_samp со своим родным братом dataтекстовое значение.
VBScript:
Set nodes = xmlFile.selectNodes("//type")
For Each node in nodes
If node.text = "PHONE" Then
Set phoneNode = xmlFile.CreateElement("phone")
For Each child in node.ParentNode.ChildNodes
If child.nodeName = "data" Then phoneNode.text = child.text
Next
For Each child in node.ParentNode.ParentNode.ParentNode.ChildNodes
If child.nodeName = "b_samp" Then child.appendChild phoneNode
Next
End If
Next
Затем сохраните файл с новым именем, чтобы оставить оригинал без изменений, и импортируйте его с новой схемой, добавив эту строку сразу после b_six элемент:
<xsd:element name = "phone" type = "xsd:string" sql:field = "phone" />
Я написал бы хранимую процедуру в SQL-Server и передал бы XML как есть. Используйте возможности SQL Server для чтения XML без схемы.
Следующий код извлечет всю информацию из данного XML.
Просто сделай что-то вроде
CREATE dbo.ImportMyXML(@xml XML)
AS
BEGIN
--Code here
END
GO
В пределах BEGIN END Вы пишете этот запрос (закомментировал INSERT INTO чтобы проверить результат запроса:
--INSERT INTO YourTargetTable(col1,col2,col3,...)
SELECT @xml.value(N'(/samp_catalog/file/name/text())[1]',N'nvarchar(max)') AS name
,a_samp.value(N'(*/text())[1]',N'nvarchar(max)') AS a_one
,a_samp.value(N'(*/text())[2]',N'nvarchar(max)') AS a_two
,a_samp.value(N'(*/text())[3]',N'nvarchar(max)') AS a_three
,b_samp.value(N'@version',N'nvarchar(max)') AS b_version
,b_samp.value(N'(*/text())[1]',N'nvarchar(max)') AS b_one
,b_samp.value(N'(*/text())[2]',N'nvarchar(max)') AS b_two
,b_samp.value(N'(*/text())[3]',N'nvarchar(max)') AS b_three
,NodesWith_c_entry.value(N'(../@version)[1]',N'nvarchar(max)') AS c_version
,NodesWith_c_entry.value(N'(total_size/text())[1]','int') AS c_total_size
,NodesWith_c_entry.value(N'(sig/text())[1]','nvarchar(max)') AS c_sig
,NodesWith_c_entry.value(N'(type/text())[1]','nvarchar(max)') AS c_type
,NodesWith_c_entry.value(N'(data_size/text())[1]','int') AS c_data_size
,NodesWith_c_entry.value(N'(data/@encoding)[1]','nvarchar(max)') AS c_data_encoding
,NodesWith_c_entry.value(N'(data/text())[1]','nvarchar(max)') AS c_data_text
FROM @xml.nodes(N'samp_catalog/file/xyz') AS A(xyz)
OUTER APPLY xyz.nodes('a_samp') AS B(a_samp)
OUTER APPLY xyz.nodes('b_samp') AS C(b_samp)
OUTER APPLY xyZ.nodes('*/c_entry') AS D(NodesWith_c_entry);
С данным XML результат таков:
+---------+-------+-------+---------+-----------+--------+--------+---------+-----------+--------------+--------+--------------+-------------+-----------------+-------------+
| name | a_one | a_two | a_three | b_version | b_one | b_two | b_three | c_version | c_total_size | c_sig | c_type | c_data_size | c_data_encoding | c_data_text |
+---------+-------+-------+---------+-----------+--------+--------+---------+-----------+--------------+--------+--------------+-------------+-----------------+-------------+
| 123.wav | 1 | 2 | 3 | 2.0 | sample | sample | sample | 1.0 | 0 | sample | SAMPLE | 256 | string | NULL |
+---------+-------+-------+---------+-----------+--------+--------+---------+-----------+--------------+--------+--------------+-------------+-----------------+-------------+
| 123.wav | 1 | 2 | 3 | 2.0 | sample | sample | sample | 1.0 | 0 | sample | PHONE | 256 | string | 15555551212 |
+---------+-------+-------+---------+-----------+--------+--------+---------+-----------+--------------+--------+--------------+-------------+-----------------+-------------+
| 123.wav | 1 | 2 | 3 | 2.0 | sample | sample | sample | 1.0 | 0 | sample | OTHER_SAMPLE | 256 | string | NULL |
+---------+-------+-------+---------+-----------+--------+--------+---------+-----------+--------------+--------+--------------+-------------+-----------------+-------------+