XQUERY XML document with mixed content model on Complex Types

  • 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