Question

KuzeyI on Wed, 05 Apr 2017 20:18:57


I'm importing XML file in the following format. I try to match XML record count to table rowcount after imported to a temp table. They do not match. Why?

SELECT  @XML.value('count(/InterfaceMessageBody/CAggregates/CId)', 'int')
--174

	INSERT INTO CAGGREGATES_Delta
	SELECT 
		S.X.value('(CId/text())[1]', 'nvarchar(100)') as CId,
		S.X.value('(NumberA/text())[1]', 'nvarchar(100)') as NumberA,
		S.X.value('(NumberB/text())[1]', 'nvarchar(100)') as NumberB,
		S.X.value('(NumberC/text())[1]', 'nvarchar(100)') as NumberC,
		S.X.value('(LatestB/LatestBDate/text())[1]', 'nvarchar(100)') as LatestBDate,
		S.X.value('(LatestB/LatestBType/text())[1]', 'nvarchar(100)') as LatestBType,
		S.X.value('(LatestM/LatestMDate/text())[1]', 'nvarchar(100)') as LatestMDate,
		S.X.value('(LatestM/LatestMType/text())[1]', 'nvarchar(100)') as LatestMType
	FROM @XML.nodes('/InterfaceMessageBody/CAggregates') as S(X);

select distinct cid from CAGGREGATES_Delta;
--2908

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="InterfaceMessageBody">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="CAggregates">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="CId" type="xs:string" />
              <xs:element name="NumberA" type="xs:integer" />
              <xs:element name="NumberB" type="xs:integer" />
              <xs:element name="NumberC" type="xs:integer" />
              <xs:element name="LatestB">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="LatestBDate" type="xs:dateTime" />
                    <xs:element name="LatestBType" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
              <xs:element name="LatestM">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="LatestMDate" type="xs:dateTime" />
                    <xs:element name="LatestMType" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>


Sponsored



Replies

Erland Sommarskog on Wed, 05 Apr 2017 21:45:12


It would have helped with some sample data that demonstrated the problem. But given the gross difference, it should be quite simple to get a feeling where the difference comes from. Are there less than 200 Cid in the XML or are there more than 2000? If there are less 200, what duplicates to you have in the table?

Yitzhak Khabinsky on Thu, 06 Apr 2017 15:14:12


Hi KuzeyI,

  • We also don't see how @xml variable gets populated from XML file on the file system.
  • I am assuming that Cid values should be unique.
    It is a good idea to have a unique index on the cid column in the DB table.