Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Unanswered: Newbie Questions - Insert From XML

    Hi!

    I'm trying to write a stored procedure that will parse XML attributes and populate columns within a DB with the stripped data. I'm a complete novice who prior to this week knew nothing about SQL commands, I've tried to bring myself up to speed at least enough to be dangerous. My understanding at least is that I need to perform a bulk insert. I'm hoping someone might be able to use the example XML code and table/column information below and provide some input.

    Example XML file:

    <?xml version="1.0" encoding="ISO-8859-1"?>
    <!DOCTYPE Asset_Collection SYSTEM "Asset_Collection.dtd">
    <Asset_Collection>
    <Collection_Metadata
    Name="Asset Collection"
    Description="Random XML Feed Test"
    Creation_Date="2010-03-30"
    Start_Date="2010-03-30"
    End_Date="2010-04-29"
    />
    <Asset Asset_Type="Lost my assets at the Casino">
    <Distribution Type="Shakedown"/>
    <Asset_Metadata>
    <App_Data Name="ProdID" Value="12345" />
    <App_Data Name="CustomerID" Value="Joe Blow" />
    <App_Data Name="AreaCode" Value="708" />
    </Asset_Metadata>
    </Asset>
    </Asset_Collection>


    Table/Columns which need to be inserting into:
    Table:
    TABLE_A

    Columns:
    ProdID
    CustomerID
    AreaCode


    Best Regards,
    Brian

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Insert From XML

    I don't use Bulk Insert often, however, with XML declaration and DTD stripped, you may be use the following in a Stored Procedure:
    Code:
    declare @xmldoc xml;
    set @xmldoc = N'<Asset_Collection>
      <Collection_Metadata Name="Asset Collection" Description="Random XML Feed Test" Creation_Date="2010-03-30" Start_Date="2010-03-30" End_Date="2010-04-29" />
      <Asset Asset_Type="Lost my assets at the Casino">
        <Distribution Type="Shakedown" />
        <Asset_Metadata>
          <App_Data Name="ProdID" Value="12345" />
          <App_Data Name="CustomerID" Value="Joe Blow" />
          <App_Data Name="AreaCode" Value="708" />
        </Asset_Metadata>
      </Asset>
    </Asset_Collection>'
    
    select
      A.C.value('(App_Data[@Name="ProdID"]/@Value)[1]','varchar(128)') as ProdID
     ,A.C.value('(App_Data[@Name="CustomerID"]/@Value)[1]','varchar(128)') as CustomerID
     ,A.C.value('(App_Data[@Name="AreaCode"]/@Value)[1]','varchar(128)') as AreaCode
    from @xmldoc.nodes('//Asset_Metadata') A(C)
    You can change any of the 'varchar(128)' datatypes to 'int', if you need.

  3. #3
    Join Date
    Nov 2011
    Posts
    3
    I appreciate the reply, the procedure posted works but I was wondering if there's a way to avoid having to list all values in the Stored Procedure. The example XML I posted only contains ProdID, CustomerID and AreaCode, the actual XML used in my environment can have hundreds of lines of data. If there's a way to pull all the data in quotes and create two columns without having to specify each attribute verbatim, please let me know. Also the values will change periodically so I need to refrain from hard coding them into the procedure. If there's anything I'm unclear on, let me know...I'll do my best to provide the proper context.

    Thanks,
    Brian

  4. #4
    Join Date
    Nov 2011
    Posts
    3
    I should also add that I'm wanting the stored procedure to pull the data from a file located at \\<servername>\<pathto>\file.xml. So hopefully if I can pull using the same stored procedure that would be great.

  5. #5
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Insert From XML

    In that case, you may want to consider using Dynamic SQL to create a PIVOT table, such as...

    Code:
    declare @xmldoc xml;
    set @xmldoc = N'<Asset_Collection>
      <Collection_Metadata Name="Asset Collection" Description="Random XML Feed Test" Creation_Date="2010-03-30" Start_Date="2010-03-30" End_Date="2010-04-29" />
      <Asset Asset_Type="Lost my assets at the Casino">
        <Distribution Type="Shakedown" />
        <Asset_Metadata>
          <App_Data Name="ProdID" Value="12345" />
          <App_Data Name="CustomerID" Value="Joe Blow" />
          <App_Data Name="AreaCode" Value="708" />
        </Asset_Metadata>
      </Asset>
    </Asset_Collection>'
    
    declare @pivotlist nvarchar(max) ,@dsql nvarchar(max);
    
    ;with appdata (names) as
    ( select ', [' + A.C.value('@Name[1]','sysname') + ']'
      from @xmldoc.nodes('//Asset_Metadata/App_Data') A(C)
      for xml path('')
    )
    select @pivotlist = stuff(names,1,2,'') from appdata
    
    set @dsql = N'
    select * from (
    select
      A.C.value(''@Name[1]'',''sysname'') as DName
     ,A.C.value(''@Value[1]'',''sysname'') as DValue
    from @xdoc.nodes(''//Asset_Metadata/App_Data'') A(C)
    ) a
    pivot (MIN(DValue) for DName in (' + @pivotlist + ')) p'
    
    exec sp_executesql @dsql ,N'@xdoc xml' ,@xdoc = @xmldoc
    For a more in-depth example of dynamically creating PIVOT tables, click here.
    Last edited by homerow; 11-17-11 at 12:49.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •