Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XQUERY XML document with mixed content model on Complex Types Expand / Collapse
Author
Message
Posted Wednesday, November 3, 2010 9:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 21, Visits: 271
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)



Post #1015353
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse