0

I'm trying to generate an XML file with custom tags and attributes from sql table data using SSIS. I have written C# script that is writing data from object variable but it is giving me an error "Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb". Please help.

Thanks in advance

csharpdev
  • 13
  • 3
  • `Object` variable is not an ADODB recordset, it is an OLEDB recordset. Here is the [article with a code sample](https://stackoverflow.com/questions/21011550/reading-object-variable-values-in-ssis-script-component-source) – Ferdipux Nov 24 '20 at 15:22
  • Hi Ferdipux, Thanks for your reply I used the below code to convert my table data to XML file and same worked for me for one of the table. But now it is throwing the below mentioned error "System.ArgumentException: 'Object is not an ADODB.RecordSet or an ADODB.Record." DataTable dt; dt = (new DataSet("VariableName")).Tables.Add("Res"); (new OleDbDataAdapter()).Fill(dt, Dts.Variables["User::VariableName"] .Value.ToString()) Could you please help out with the solution. Thanks in advance – csharpdev Nov 26 '20 at 12:44

2 Answers2

0

You can output xml directly from MSSQL without using SSIS, you would use the column aliases to set your custom tags/attributes.

Please see below demonstration, attributes are aliased with a @ prefix and tags without any prefix, sub tags are built with /

Multiline sub tags are built with a subquery

IF OBJECT_ID('tempdb.dbo.#StackOverflowTestParent') IS NOT NULL DROP TABLE #StackOverflowTestParent
CREATE TABLE #StackOverflowTestParent
(
    ParentId BIGINT PRIMARY KEY IDENTITY(1,1),
    ParentAttribute1 VARCHAR(50),
    ParentAttribute2 VARCHAR(50),
    ParentValue VARCHAR(50)
)

INSERT INTO #StackOverflowTestParent VALUES ('Oh','Hello','World')
INSERT INTO #StackOverflowTestParent VALUES ('World','Goes','Hello')

IF OBJECT_ID('tempdb.dbo.#StackOverflowTestParent') IS NOT NULL DROP TABLE #StackOverflowTestParent
CREATE TABLE #StackOverflowTestChild
(
    ChildId BIGINT PRIMARY KEY IDENTITY(1,1),
    ParentId BIGINT,
    ChildAttribute1 VARCHAR(50),
    ChildAttribute2 VARCHAR(50),
    ChildValue VARCHAR(50)
)

INSERT INTO #StackOverflowTestChild VALUES (1,'Bobby','Says','Hello')
INSERT INTO #StackOverflowTestChild VALUES (1,'Dobby','Starts','Waving')
INSERT INTO #StackOverflowTestChild VALUES (2,'Dolly','Says','Hozit')
INSERT INTO #StackOverflowTestChild VALUES (2,'Sarah','Says','Hi')

    IF OBJECT_ID('tempdb.dbo.#StackOverflowTestParent') IS NOT NULL DROP TABLE #StackOverflowTestParent
CREATE TABLE #StackOverflowTestParent
(
    ParentId BIGINT PRIMARY KEY IDENTITY(1,1),
    ParentAttribute1 VARCHAR(50),
    ParentAttribute2 VARCHAR(50),
    ParentValue VARCHAR(50)
)

INSERT INTO #StackOverflowTestParent VALUES ('Oh','Hello','World')
INSERT INTO #StackOverflowTestParent VALUES ('World','Goes','Hello')

IF OBJECT_ID('tempdb.dbo.#StackOverflowTestParent') IS NOT NULL DROP TABLE #StackOverflowTestParent
CREATE TABLE #StackOverflowTestChild
(
    ChildId BIGINT PRIMARY KEY IDENTITY(1,1),
    ParentId BIGINT,
    ChildAttribute1 VARCHAR(50),
    ChildAttribute2 VARCHAR(50),
    ChildValue VARCHAR(50)
)

INSERT INTO #StackOverflowTestChild VALUES (1,'Bobby','Says','Hello')
INSERT INTO #StackOverflowTestChild VALUES (1,'Dobby','Starts','Waving')
INSERT INTO #StackOverflowTestChild VALUES (2,'Dolly','Says','Hozit')
INSERT INTO #StackOverflowTestChild VALUES (2,'Sarah','Says','Hi')

;WITH XMLNAMESPACES ( 'www.example.com' AS ns0)
SELECT P.ParentId AS '@ParentId',
       P.ParentAttribute1 AS '@CustomAttribute1',
       P.ParentAttribute2 AS 'ParentSub/@CustomAttribute2',
       P.ParentValue AS 'StackOverflowTestMessage',
       /* Building child node with a subquery*/
       (SELECT C.ChildId AS '@ChildUniqueId',
               C.ChildAttribute1 AS '@ChildAttribute1',
               C.ChildAttribute2 AS 'SubChildSingle/@ChildAttribute2',
               C.ChildValue AS 'StackOverflowTestChild'
         FROM #StackOverflowTestChild C
         WHERE C.ParentId = P.ParentId
         FOR XML PATH('CustomChildNode'),TYPE)

FROM #StackOverflowTestParent P
FOR XML PATH('StackOverflowTestParent'),ROOT('ns0:Root')

Result

<ns0:Root xmlns:ns0="www.example.com">
  <StackOverflowTestParent ParentId="1"
                           CustomAttribute1="Oh">
    <ParentSub CustomAttribute2="Hello" />
    <StackOverflowTestMessage>World</StackOverflowTestMessage>
    <CustomChildNode xmlns:ns0="www.example.com"
                     ChildUniqueId="1"
                     ChildAttribute1="Bobby">
      <SubChildSingle ChildAttribute2="Says" />
      <StackOverflowTestChild>Hello</StackOverflowTestChild>
    </CustomChildNode>
    <CustomChildNode xmlns:ns0="www.example.com"
                     ChildUniqueId="2"
                     ChildAttribute1="Dobby">
      <SubChildSingle ChildAttribute2="Starts" />
      <StackOverflowTestChild>Waving</StackOverflowTestChild>
    </CustomChildNode>
  </StackOverflowTestParent>
  <StackOverflowTestParent ParentId="2"
                           CustomAttribute1="World">
    <ParentSub CustomAttribute2="Goes" />
    <StackOverflowTestMessage>Hello</StackOverflowTestMessage>
    <CustomChildNode xmlns:ns0="www.example.com"
                     ChildUniqueId="3"
                     ChildAttribute1="Dolly">
      <SubChildSingle ChildAttribute2="Says" />
      <StackOverflowTestChild>Hozit</StackOverflowTestChild>
    </CustomChildNode>
    <CustomChildNode xmlns:ns0="www.example.com"
                     ChildUniqueId="4"
                     ChildAttribute1="Sarah">
      <SubChildSingle ChildAttribute2="Says" />
      <StackOverflowTestChild>Hi</StackOverflowTestChild>
    </CustomChildNode>
  </StackOverflowTestParent>
</ns0:Root>

Edit

Here is another example of how create a basic version with both namespaces

/* Here is a more basic version */
;WITH XMLNAMESPACES ( 'www.example.com' AS ns0, 'w3/XMLSchema-instance' AS xsi)
SELECT P.ParentAttribute1 AS 'ColumnNames/ParentAttribute1',
       P.ParentAttribute2 AS 'ColumnNames/ParentAttribute2',
       P.ParentValue AS 'ColumnNames/ParentValue'
FROM #StackOverflowTestParent P
FOR XML PATH('CustomTag'),ROOT('ns0:Root')

Result

<ns0:Root xmlns:xsi="w3/XMLSchema-instance" xmlns:ns0="www.example.com">
  <CustomTag>
    <ColumnNames>
      <ParentAttribute1>Oh</ParentAttribute1>
      <ParentAttribute2>Hello</ParentAttribute2>
      <ParentValue>World</ParentValue>
    </ColumnNames>
  </CustomTag>
  <CustomTag>
    <ColumnNames>
      <ParentAttribute1>World</ParentAttribute1>
      <ParentAttribute2>Goes</ParentAttribute2>
      <ParentValue>Hello</ParentValue>
    </ColumnNames>
  </CustomTag>
</ns0:Root>
Ockert
  • 425
  • 4
  • 6
  • @csharpdev , you add namespaces with the *with* statement, have updated the code to demonstrate. For version and char encoding I usually just add it in C#, String xml = "" + sqlXMLOutput – Ockert Nov 25 '20 at 15:02
  • What do you mean by embed attributes? You can add an attribute or a value to any node using column aliases? – Ockert Nov 25 '20 at 15:04
  • Hi Ockert, Thanks a lot for your reply and the sql script, this helps a lot Actually I need to generate the table data in XML file in a certain format like Could you please let me know how add the same using SQL scripts. Thanks in advance! – csharpdev Nov 26 '20 at 12:43
  • @csharpdev, have added in another example, you need to update the path names and aliases to build what you want – Ockert Nov 26 '20 at 13:16
  • Thanks a lot Ockert for your help – csharpdev Nov 26 '20 at 15:58
0

Corrected C# code to convert Object variable table to ADO.NET Table, based on original question.

DataTable dt = new DataTable();
(new OleDbDataAdapter()).Fill(dt, Dts.Variables["User::VariableName"].Value);

Hint - you should provide the object itself, not its conversion to String, as in code fragment. That was the reason of errors like

Object is not an ADODB.RecordSet or an ADODB.Record.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33