none
XML parsing in SQL Server 2008

    Question

  • Guys,

    I have a xml file such structure:

      <!DOCTYPE datalist (View Source for full doctype...)> 
    - <datalist>
    - <element>
    - <field>
      <field-name>Title</field-name> 
      <field-value>Change Direct Report Responsible Manager for US DSO - Intersystem FOBO - DBMI US Region</field-value> 
      </field>
    - <field>
      <field-name>Business Priority</field-name> 
      <field-value>2. Should have</field-value> 
      </field>
    - <field>
      <field-name>Description</field-name> 
      <field-value>Hi Fedor, Please update mapping table by replacing Nicole Lashley with Denielle Hosten For FOBO report. Attached report is for your ref. Report download path - DBMI (US)> Packs> Us Securities DSO>Intersystem FOBO. Thanks.</field-value> 
      </field>
    - <field>
      <field-name>Priority</field-name> 
      <field-value>3</field-value> 
      </field>
      </element>
    - <element>

    I need put it into the table with columns: Title, Business Priority, etc.

    How is the easiest way to do it?

    Thanks!

    Thursday, April 26, 2012 1:43 PM

Answers

  • Guys, it works!!!

    I've done the next way:

    DECLARE @Data XML;
    CREATE TABLE #Xml ( Data XML );
    INSERT  INTO #Xml
            ( Data        
            )
            SELECT  CONVERT(XML,BulkColumn,2)
            FROM    OPENROWSET(BULK N'D:\1.xml', SINGLE_BLOB) O;
    SELECT @Data = Data FROM #Xml ;
    DROP TABLE #Xml;
    SELECT  Element.value('field[field-name="Title"][1]/field-value[1]', 'NVARCHAR(255)') AS Title ,
            Element.value('field[field-name="Business PRiority"][1]/field-value[1]', 'NVARCHAR(255)') AS Title ,
    		Element.value('field[field-name="Description"][1]/field-value[1]', 'NVARCHAR(255)') AS [Description] ,
    		Element.value('field[field-name="Priority"][1]/field-value[1]', 'NVARCHAR(255)') AS Priority
    FROM    @Data.nodes('datalist/element') Datalist ( Element );

    • Marked as answer by SunnyInga Thursday, April 26, 2012 3:19 PM
    Thursday, April 26, 2012 3:19 PM

All replies

  • I'd use an SSIS package with an XML datasource

    Chuck

    Thursday, April 26, 2012 1:52 PM
  • Or use BULK INSERT and parse it yourself:

    Parsing:

    DECLARE @Data XML;
    
    --Bulk load:
    --CREATE TABLE #Xml ( Data XML );
    --INSERT  INTO #Xml
    --        ( Data        
    --        )
    --        SELECT  *
    --        FROM    OPENROWSET(BULK N'yourFileGoesHere', SINGLE_BLOB) O;
    --SELECT @Data = Data FROM #Xml ;
    --DROP TABLE #Xml;
    
    --Or a simple assignment:
    SET @Data = '
     <datalist>
     <element>
     <field>
      <field-name>Title</field-name> 
      <field-value>Change Direct Report Responsible Manager for US DSO - Intersystem FOBO - DBMI US Region</field-value> 
      </field>
     <field>
      <field-name>Business Priority</field-name> 
      <field-value>2. Should have</field-value> 
      </field>
     <field>
      <field-name>Description</field-name> 
      <field-value>Hi Fedor, Please update mapping table by replacing Nicole Lashley with Denielle Hosten For FOBO report. Attached report is for your ref. Report download path - DBMI (US)> Packs> Us Securities DSO>Intersystem FOBO. Thanks.</field-value> 
      </field>
     <field>
      <field-name>Priority</field-name> 
      <field-value>3</field-value> 
      </field>
      </element>
    <element>
     <field>
      <field-name>Title</field-name> 
      <field-value>Change 2</field-value> 
      </field>
      </element>
      </datalist>
    ';
    
    SELECT  Element.value('field[field-name="Title"][1]/field-value[1]', 'NVARCHAR(255)') AS Title ,
    		Element.value('field[field-name="Description"][1]/field-value[1]', 'NVARCHAR(255)') AS [Description] ,
    		Element.value('field[field-name="Priority"][1]/field-value[1]', 'NVARCHAR(255)') AS Priority
    FROM    @Data.nodes('/datalist/element') Datalist ( Element );

    Thursday, April 26, 2012 2:04 PM
  • I tried to use bulk insert and took error:

    Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support.

    Thursday, April 26, 2012 2:23 PM
  • And have you tried using CONVERT?
    Thursday, April 26, 2012 2:56 PM
  • Guys,

    Could you possible advise why I get empty table from the following query?

    DECLARE @x XML;
    SET @x = CONVERT(XML, 'D:\1.xml', 2);
    SELECT  Element.value('field[field-name="Title"][1]/field-value[1]', 'NVARCHAR(255)') AS Title ,
            Element.value('field[field-name="Business PRiority"][1]/field-value[1]', 'NVARCHAR(255)') AS Title ,
    		Element.value('field[field-name="Description"][1]/field-value[1]', 'NVARCHAR(255)') AS [Description] ,
    		Element.value('field[field-name="Priority"][1]/field-value[1]', 'NVARCHAR(255)') AS Priority
    FROM    @x.nodes('datalist/element') Datalist ( Element );

    Thanks
    Thursday, April 26, 2012 2:57 PM
  • This does nothing except try to convert the string 'D:\1.xml' into an XML type:  SET @x = CONVERT(XML, 'D:\1.xml', 2);

    You need to use OPENROWSET

    FROM    (SELECT *   
        FROM OPENROWSET (BULK 'D:\1.xml', SINGLE_CLOB)

    Look at Stephens example a few posts up


    Chuck



    Thursday, April 26, 2012 3:02 PM
  • YEs, exactly. But I have error about converting taht I mentioned above. How to solve it in this case?
    Thursday, April 26, 2012 3:12 PM
  • Guys, it works!!!

    I've done the next way:

    DECLARE @Data XML;
    CREATE TABLE #Xml ( Data XML );
    INSERT  INTO #Xml
            ( Data        
            )
            SELECT  CONVERT(XML,BulkColumn,2)
            FROM    OPENROWSET(BULK N'D:\1.xml', SINGLE_BLOB) O;
    SELECT @Data = Data FROM #Xml ;
    DROP TABLE #Xml;
    SELECT  Element.value('field[field-name="Title"][1]/field-value[1]', 'NVARCHAR(255)') AS Title ,
            Element.value('field[field-name="Business PRiority"][1]/field-value[1]', 'NVARCHAR(255)') AS Title ,
    		Element.value('field[field-name="Description"][1]/field-value[1]', 'NVARCHAR(255)') AS [Description] ,
    		Element.value('field[field-name="Priority"][1]/field-value[1]', 'NVARCHAR(255)') AS Priority
    FROM    @Data.nodes('datalist/element') Datalist ( Element );

    • Marked as answer by SunnyInga Thursday, April 26, 2012 3:19 PM
    Thursday, April 26, 2012 3:19 PM