locked
How do I build a schema to load multiple tables where the id for join between the tables does not exist in the XML file? RRS feed

  • Question

  • Due to some issues with SSIS and our current configuration I have had to resort to using SQLXMLBulkLoad to load a previously existing XML file into our database.  I have been scouring the web trying to find the answer for a few days now.  I will do my best to illustrate the issue.

    I need to have the joining id’s generated at load to show the relationship due to multiple surveys being loaded through one file.  As you should see in the file below the ID fields are not contained in the XML file.

    SurveyTable

    (Survey_ID         Int

    SurveyNumber    VarChar(500))

    QuestionTable

    (Survey_ID       Int,

    Question_ID     Int,

    QuestionText    VarChar(500))

     AnswerTable

    (Question_ID    Int,

    Answer_ID       Int,

    AnswerText      VarChar(500)

     

    XML File

      <Survey>

        <SurveyNum>126288569</SurveyNum>

           <question>

           <questiontext>WC27 Return</questiontext>

           <Answer>

             <AnswerText>Good</AnswerText>

            </Answer>

           </question>

      </Survey>

    End Result Expected

    SurveyTable

    1                     126288569

    QuestionTable

    1          1          WC27 Return

    Answer Table

    1              Good

     

    Thursday, September 15, 2011 8:55 PM

Answers

  • I got this to work:

    USE tempdb
    GO
    
    -- Reset
    IF OBJECT_ID( 'dbo.Answer' ) IS NOT NULL DROP TABLE dbo.Answer
    IF OBJECT_ID( 'dbo.Question' ) IS NOT NULL DROP TABLE dbo.Question
    IF OBJECT_ID( 'dbo.Survey' ) IS NOT NULL DROP TABLE dbo.Survey
    GO
    
    IF OBJECT_ID( 'dbo.Survey' ) IS NULL
    CREATE TABLE dbo.Survey(
    	Survey_ID		INT IDENTITY PRIMARY KEY,
    	SurveyNumber	VARCHAR(500),
    	dateAdded		DATETIME DEFAULT GETDATE()
    )
    GO
    
    IF OBJECT_ID( 'dbo.Question' ) IS NULL
    CREATE TABLE dbo.Question(
    	Question_ID		INT IDENTITY(100,1) PRIMARY KEY,
    	Survey_ID		INT NOT NULL,
    	QuestionText	VARCHAR(500)
    
    	CONSTRAINT FK_Question__Survey FOREIGN KEY ( Survey_ID ) 
    		REFERENCES dbo.Survey ( Survey_ID )
    )
    GO
    
    IF OBJECT_ID( 'dbo.Answer' ) IS NULL
    CREATE TABLE dbo.Answer(
    	Answer_ID		INT IDENTITY(200,1) PRIMARY KEY,
    	Question_ID		INT,
    	AnswerText		VARCHAR(500)
    
    	CONSTRAINT FK_Answer__Question FOREIGN KEY ( Question_ID ) 
    		REFERENCES dbo.Question ( Question_ID )
    )
    GO
    

    XSD:

    <?xml version="1.0" encoding="utf-8"?>
    <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
    
      <xs:annotation>
        <xs:appinfo>
          <sql:relationship name="Survey__Question"
                parent="Survey"
                parent-key="Survey_ID"
                child="Question"
                child-key="Survey_ID" />
    
          <sql:relationship name="Question__Answer"
            parent="Question"
            parent-key="Question_ID"
            child="Answer"
            child-key="Question_ID" />
        </xs:appinfo>
      </xs:annotation>
    
      <xs:element name="Survey" sql:relation="Survey">
        <xs:complexType>
          <xs:sequence>
            <xs:element name="SurveyNum" sql:field="SurveyNumber" type="xs:unsignedInt" />
            <xs:element name="question" sql:relation="Question" sql:relationship="Survey__Question">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="questiontext" type="xs:string" />
                  <xs:element name="Answer" sql:relation="Answer" sql:relationship="Question__Answer">
                    <xs:complexType>
                      <xs:sequence>
                        <xs:element name="AnswerText" type="xs:string" />
                      </xs:sequence>
                    </xs:complexType>
                  </xs:element>
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>
    

    • Marked as answer by JonW626 Tuesday, September 20, 2011 1:44 PM
    Friday, September 16, 2011 12:39 PM
    Answerer
  • Actually, I can also reproduce this if the KeepIdentity property for the bulk load object is set to True ( which is the default ).  Try setting the KeepIdentity property to False, eg (this is my simplified VBScript ):

    Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
    objBL.ConnectionString = "provider=SQLOLEDB;data source=.\yourServer;database=tempdb;integrated security=SSPI"
    
    objBL.KeepIdentity = False
    
    objBL.Execute "test.xsd", "test.xml"
    Set objBL = Nothing
    
    Msgbox "Finished!"
    

    HTH

    • Marked as answer by JonW626 Tuesday, September 20, 2011 1:44 PM
    Tuesday, September 20, 2011 1:17 PM
    Answerer

All replies

  • I got this to work:

    USE tempdb
    GO
    
    -- Reset
    IF OBJECT_ID( 'dbo.Answer' ) IS NOT NULL DROP TABLE dbo.Answer
    IF OBJECT_ID( 'dbo.Question' ) IS NOT NULL DROP TABLE dbo.Question
    IF OBJECT_ID( 'dbo.Survey' ) IS NOT NULL DROP TABLE dbo.Survey
    GO
    
    IF OBJECT_ID( 'dbo.Survey' ) IS NULL
    CREATE TABLE dbo.Survey(
    	Survey_ID		INT IDENTITY PRIMARY KEY,
    	SurveyNumber	VARCHAR(500),
    	dateAdded		DATETIME DEFAULT GETDATE()
    )
    GO
    
    IF OBJECT_ID( 'dbo.Question' ) IS NULL
    CREATE TABLE dbo.Question(
    	Question_ID		INT IDENTITY(100,1) PRIMARY KEY,
    	Survey_ID		INT NOT NULL,
    	QuestionText	VARCHAR(500)
    
    	CONSTRAINT FK_Question__Survey FOREIGN KEY ( Survey_ID ) 
    		REFERENCES dbo.Survey ( Survey_ID )
    )
    GO
    
    IF OBJECT_ID( 'dbo.Answer' ) IS NULL
    CREATE TABLE dbo.Answer(
    	Answer_ID		INT IDENTITY(200,1) PRIMARY KEY,
    	Question_ID		INT,
    	AnswerText		VARCHAR(500)
    
    	CONSTRAINT FK_Answer__Question FOREIGN KEY ( Question_ID ) 
    		REFERENCES dbo.Question ( Question_ID )
    )
    GO
    

    XSD:

    <?xml version="1.0" encoding="utf-8"?>
    <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
    
      <xs:annotation>
        <xs:appinfo>
          <sql:relationship name="Survey__Question"
                parent="Survey"
                parent-key="Survey_ID"
                child="Question"
                child-key="Survey_ID" />
    
          <sql:relationship name="Question__Answer"
            parent="Question"
            parent-key="Question_ID"
            child="Answer"
            child-key="Question_ID" />
        </xs:appinfo>
      </xs:annotation>
    
      <xs:element name="Survey" sql:relation="Survey">
        <xs:complexType>
          <xs:sequence>
            <xs:element name="SurveyNum" sql:field="SurveyNumber" type="xs:unsignedInt" />
            <xs:element name="question" sql:relation="Question" sql:relationship="Survey__Question">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="questiontext" type="xs:string" />
                  <xs:element name="Answer" sql:relation="Answer" sql:relationship="Question__Answer">
                    <xs:complexType>
                      <xs:sequence>
                        <xs:element name="AnswerText" type="xs:string" />
                      </xs:sequence>
                    </xs:complexType>
                  </xs:element>
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>
    

    • Marked as answer by JonW626 Tuesday, September 20, 2011 1:44 PM
    Friday, September 16, 2011 12:39 PM
    Answerer
  • Thanks for the reply!

    I received this error when trying to do the above example (I have included my XML source file)...Any ideas?

     

    - <Survey>
      <SurveyNum>126288569</SurveyNum>
    - <Question>
      <QuestionText>This is my question</QuestionText>
    - <Answer>
      <AnswerText>This is my answer</AnswerText>
      </Answer>
      </Question>
      </Survey>

    No data was provided for column 'Survey_ID' on table 'Question', and this column cannot contain NULL values.

    Tuesday, September 20, 2011 12:53 PM
  • Do you have the same IDENTITY and FOREIGN KEY setup as in my demo?

    I would guess you are missing the foreign key from dbo.Question to dbo.Survey.  I would start with my demo, using tempdb, get it working with a simple bit of VBScript and go from there.

    You should also know that XML ( and thing to do with XML like XQuery ) are case-sensitive.  The case of you XML has changed in your different posts so you need to take account of this in the XSD.  This could also account for problems.

    Tuesday, September 20, 2011 1:09 PM
    Answerer
  • Actually, I can also reproduce this if the KeepIdentity property for the bulk load object is set to True ( which is the default ).  Try setting the KeepIdentity property to False, eg (this is my simplified VBScript ):

    Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
    objBL.ConnectionString = "provider=SQLOLEDB;data source=.\yourServer;database=tempdb;integrated security=SSPI"
    
    objBL.KeepIdentity = False
    
    objBL.Execute "test.xsd", "test.xml"
    Set objBL = Nothing
    
    Msgbox "Finished!"
    

    HTH

    • Marked as answer by JonW626 Tuesday, September 20, 2011 1:44 PM
    Tuesday, September 20, 2011 1:17 PM
    Answerer
  • wBob,

    Setting that property made it work!  Thanks for all the help...You would be great at developing training for XML stuff...I tried a lot of different ways to find these answers searching the web and couldn't find any real good guidence!  This was a great help!

     Thanks,

     Jon

    Tuesday, September 20, 2011 1:43 PM