November 3, 2010 at 9:37 am
Hello,
I have a problem to import data from XML with mixed content model and Complex Types.
I use XQUERY in order to see data, but this does not return rows
What is wrong with my XQUERY ?
Thanks Olego
Below I put XML file:
----------------------------------------------------
-- test.xml
<changesynergy_report xmlns="ChangeSynergy-Namespace">
<changesynergy_report_attributes>
<changesynergy_report_item>
<attribute_item>
<name>problem_number</name>
<value>1</value>
<type>CCM_STRING</type>
</attribute_item>
<attribute_item>
<name>crstatus</name>
<value>concluded</value>
<type>CCM_STRING</type>
</attribute_item>
<attribute_item>
<name>problem_synopsis</name>
<value>Quad LAN lose packets</value>
<type>CCM_STRING</type>
</attribute_item>
<attribute_item>
<name>problem_description</name>
<value>When used on-board LAN - there are no lost packets.
</value>
<type>CCM_EXTENDED_TEXT</type>
</attribute_item>
<attribute_item>
<name>severity</name>
<value>1 - Critical</value>
<type>CCM_LISTBOX</type>
</attribute_item>
<attribute_item>
<name>target_release</name>
<value>4.1</value>
<type>CCM_LISTBOX</type>
</attribute_item>
<attribute_item>
<name>priority</name>
<value>2 - High</value>
<type>CCM_LISTBOX</type>
</attribute_item>
<attribute_item>
<name>customer_name</name>
<value />
<type>CCM_STRING</type>
</attribute_item>
</changesynergy_report_item>
</changesynergy_report_attributes>
</changesynergy_report>
------------------------------------
--TEST.XSD generated by XML Source component of SSIS
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="ChangeSynergy-Namespace" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="changesynergy_report">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="changesynergy_report_attributes">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="changesynergy_report_item">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="attribute_item">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="name" type="xs:string" />
<xs:element minOccurs="0" name="value">
<xs:complexType mixed="true">
<xs:sequence minOccurs="0">
<xs:element minOccurs="0" maxOccurs="unbounded" name="br" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="type" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
--------------------------------------------
DECLARE @OBJECT_ID INT
DECLARE @x xml
DECLARE @xmlFileName VARCHAR(200)
SET @xmlFileName = 'C:\temp\test.XML'
SELECT @OBJECT_ID = OBJECT_ID('tempdb..##XmlImportTest')
IF @OBJECT_ID IS NOT NULL
DROP TABLE ##XmlImportTest
CREATE TABLE ##XmlImportTest
(
xmlFileName VARCHAR(300),
xml_data xml
)
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('
INSERT INTO ##XmlImportTest(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM
(
SELECT CONVERT(xml, BulkColumn, 2)
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
SELECT TOP 1 @x =xml_data FROM ##XmlImportTest
select n.value('(problem_description/text())[1]', 'varchar(100)') as problem_description
from @x.nodes('/changesynergy_report/changesynergy_report_attributes/changesynergy_report_item') as t(n)
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply