How to return multiple tables as one XML?

I have the following script:

DECLARE @columns TABLE (

    Caption varchar(50),

    Width int);



INSERT INTO @columns 

VALUES ('Id', 0), ('Name', 100);



DECLARE @rows TABLE (

    Id int,

    [Name] varchar(50));



INSERT INTO @rows 

VALUES (1, 'John'), (2, 'Steve');



SELECT *,

    (SELECT *

     FROM @rows

     FOR XML PATH('Row'), ROOT('Rows'), TYPE, ELEMENTS)

FROM @columns

FOR XML PATH('Column'), ROOT('Results'), TYPE, ELEMENTS;

And I need to return the following XML:

<Results>

    <Columns>

        <Column>

            <Caption>Id</Caption>

            <Width>0</Width>

        </Column>

        <Column>

            <Caption>Name</Caption>

            <Width>100</Width>

        </Column>

    </Columns>

    <Rows>

        <Row>

            <Id>1</Id>

            <Name>John</Name>

        </Row>

        <Row>

            <Id>2</Id>

            <Name>Steve</Name>

        </Row>

    </Rows>

</Results>

The idea is that I will convert the XML into a DataSet with 2 DataTables (one for Columns and the other for Rows). I will use this to populate a DataGridView.

However, my problem is that the XML I'm generating currently is malformed and isn't the same as I'm expecting.

What is the correct syntax to generate the XML as expected?

asked 5 hours ago

Ivan-Mark Debono

4,5921457130

add a comment |

I have the following script:

DECLARE @columns TABLE (

    Caption varchar(50),

    Width int);



INSERT INTO @columns 

VALUES ('Id', 0), ('Name', 100);



DECLARE @rows TABLE (

    Id int,

    [Name] varchar(50));



INSERT INTO @rows 

VALUES (1, 'John'), (2, 'Steve');



SELECT *,

    (SELECT *

     FROM @rows

     FOR XML PATH('Row'), ROOT('Rows'), TYPE, ELEMENTS)

FROM @columns

FOR XML PATH('Column'), ROOT('Results'), TYPE, ELEMENTS;

And I need to return the following XML:

<Results>

    <Columns>

        <Column>

            <Caption>Id</Caption>

            <Width>0</Width>

        </Column>

        <Column>

            <Caption>Name</Caption>

            <Width>100</Width>

        </Column>

    </Columns>

    <Rows>

        <Row>

            <Id>1</Id>

            <Name>John</Name>

        </Row>

        <Row>

            <Id>2</Id>

            <Name>Steve</Name>

        </Row>

    </Rows>

</Results>

The idea is that I will convert the XML into a DataSet with 2 DataTables (one for Columns and the other for Rows). I will use this to populate a DataGridView.

However, my problem is that the XML I'm generating currently is malformed and isn't the same as I'm expecting.

What is the correct syntax to generate the XML as expected?

asked 5 hours ago

Ivan-Mark Debono

4,5921457130

add a comment |

I have the following script:

DECLARE @columns TABLE (

    Caption varchar(50),

    Width int);



INSERT INTO @columns 

VALUES ('Id', 0), ('Name', 100);



DECLARE @rows TABLE (

    Id int,

    [Name] varchar(50));



INSERT INTO @rows 

VALUES (1, 'John'), (2, 'Steve');



SELECT *,

    (SELECT *

     FROM @rows

     FOR XML PATH('Row'), ROOT('Rows'), TYPE, ELEMENTS)

FROM @columns

FOR XML PATH('Column'), ROOT('Results'), TYPE, ELEMENTS;

And I need to return the following XML:

<Results>

    <Columns>

        <Column>

            <Caption>Id</Caption>

            <Width>0</Width>

        </Column>

        <Column>

            <Caption>Name</Caption>

            <Width>100</Width>

        </Column>

    </Columns>

    <Rows>

        <Row>

            <Id>1</Id>

            <Name>John</Name>

        </Row>

        <Row>

            <Id>2</Id>

            <Name>Steve</Name>

        </Row>

    </Rows>

</Results>

The idea is that I will convert the XML into a DataSet with 2 DataTables (one for Columns and the other for Rows). I will use this to populate a DataGridView.

However, my problem is that the XML I'm generating currently is malformed and isn't the same as I'm expecting.

What is the correct syntax to generate the XML as expected?

asked 5 hours ago

Ivan-Mark Debono

4,5921457130

I have the following script:

DECLARE @columns TABLE (

    Caption varchar(50),

    Width int);



INSERT INTO @columns 

VALUES ('Id', 0), ('Name', 100);



DECLARE @rows TABLE (

    Id int,

    [Name] varchar(50));



INSERT INTO @rows 

VALUES (1, 'John'), (2, 'Steve');



SELECT *,

    (SELECT *

     FROM @rows

     FOR XML PATH('Row'), ROOT('Rows'), TYPE, ELEMENTS)

FROM @columns

FOR XML PATH('Column'), ROOT('Results'), TYPE, ELEMENTS;

And I need to return the following XML:

<Results>

    <Columns>

        <Column>

            <Caption>Id</Caption>

            <Width>0</Width>

        </Column>

        <Column>

            <Caption>Name</Caption>

            <Width>100</Width>

        </Column>

    </Columns>

    <Rows>

        <Row>

            <Id>1</Id>

            <Name>John</Name>

        </Row>

        <Row>

            <Id>2</Id>

            <Name>Steve</Name>

        </Row>

    </Rows>

</Results>

The idea is that I will convert the XML into a DataSet with 2 DataTables (one for Columns and the other for Rows). I will use this to populate a DataGridView.

However, my problem is that the XML I'm generating currently is malformed and isn't the same as I'm expecting.

What is the correct syntax to generate the XML as expected?

c# sql sql-server sql-server-2016

asked 5 hours ago

Ivan-Mark Debono

4,5921457130

asked 5 hours ago

Ivan-Mark Debono

4,5921457130

asked 5 hours ago

Ivan-Mark Debono

4,5921457130

asked 5 hours ago

Ivan-Mark Debono

4,5921457130

asked 5 hours ago

Ivan-Mark Debono

4,5921457130

add a comment |

2 Answers
2

active

oldest

votes

This gets you the result you want, based on the data we have:

SELECT (SELECT Caption,

               Width

        FROM @columns

        FOR XML PATH('Column'),TYPE) AS [Columns],

       (SELECT Id,

               [Name]

        FROM @rows

        FOR XML PATH('Row'),TYPE) AS [Rows]

FOR XML PATH ('Results');

edited 5 hours ago

answered 5 hours ago

Larnu

17k41630

add a comment |

This query generates exactly the result you're expected:

SELECT (SELECT clm.Caption,

               clm.Width

        FROM @columns clm

        FOR XML PATH('Column'), TYPE) AS Columns,

       (SELECT rs.Id,

               rs.[Name]

        FROM @rows rs

        FOR XML PATH('Row'),TYPE) AS [Rows]

FOR XML PATH ('Results');

answered 5 hours ago

Saman Gholami

1,87952154

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54342870%2fhow-to-return-multiple-tables-as-one-xml%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

This gets you the result you want, based on the data we have:

SELECT (SELECT Caption,

               Width

        FROM @columns

        FOR XML PATH('Column'),TYPE) AS [Columns],

       (SELECT Id,

               [Name]

        FROM @rows

        FOR XML PATH('Row'),TYPE) AS [Rows]

FOR XML PATH ('Results');

edited 5 hours ago

answered 5 hours ago

Larnu

17k41630

add a comment |

This gets you the result you want, based on the data we have:

SELECT (SELECT Caption,

               Width

        FROM @columns

        FOR XML PATH('Column'),TYPE) AS [Columns],

       (SELECT Id,

               [Name]

        FROM @rows

        FOR XML PATH('Row'),TYPE) AS [Rows]

FOR XML PATH ('Results');

edited 5 hours ago

answered 5 hours ago

Larnu

17k41630

add a comment |

This gets you the result you want, based on the data we have:

SELECT (SELECT Caption,

               Width

        FROM @columns

        FOR XML PATH('Column'),TYPE) AS [Columns],

       (SELECT Id,

               [Name]

        FROM @rows

        FOR XML PATH('Row'),TYPE) AS [Rows]

FOR XML PATH ('Results');

edited 5 hours ago

answered 5 hours ago

Larnu

17k41630

This gets you the result you want, based on the data we have:

SELECT (SELECT Caption,

               Width

        FROM @columns

        FOR XML PATH('Column'),TYPE) AS [Columns],

       (SELECT Id,

               [Name]

        FROM @rows

        FOR XML PATH('Row'),TYPE) AS [Rows]

FOR XML PATH ('Results');

edited 5 hours ago

answered 5 hours ago

Larnu

17k41630

edited 5 hours ago

answered 5 hours ago

Larnu

17k41630

answered 5 hours ago

Larnu

17k41630

answered 5 hours ago

Larnu

17k41630

add a comment |

This query generates exactly the result you're expected:

SELECT (SELECT clm.Caption,

               clm.Width

        FROM @columns clm

        FOR XML PATH('Column'), TYPE) AS Columns,

       (SELECT rs.Id,

               rs.[Name]

        FROM @rows rs

        FOR XML PATH('Row'),TYPE) AS [Rows]

FOR XML PATH ('Results');

answered 5 hours ago

Saman Gholami

1,87952154

add a comment |

This query generates exactly the result you're expected:

SELECT (SELECT clm.Caption,

               clm.Width

        FROM @columns clm

        FOR XML PATH('Column'), TYPE) AS Columns,

       (SELECT rs.Id,

               rs.[Name]

        FROM @rows rs

        FOR XML PATH('Row'),TYPE) AS [Rows]

FOR XML PATH ('Results');

answered 5 hours ago

Saman Gholami

1,87952154

add a comment |

This query generates exactly the result you're expected:

SELECT (SELECT clm.Caption,

               clm.Width

        FROM @columns clm

        FOR XML PATH('Column'), TYPE) AS Columns,

       (SELECT rs.Id,

               rs.[Name]

        FROM @rows rs

        FOR XML PATH('Row'),TYPE) AS [Rows]

FOR XML PATH ('Results');

answered 5 hours ago

Saman Gholami

1,87952154

This query generates exactly the result you're expected:

SELECT (SELECT clm.Caption,

               clm.Width

        FROM @columns clm

        FOR XML PATH('Column'), TYPE) AS Columns,

       (SELECT rs.Id,

               rs.[Name]

        FROM @rows rs

        FOR XML PATH('Row'),TYPE) AS [Rows]

FOR XML PATH ('Results');

answered 5 hours ago

Saman Gholami

1,87952154

answered 5 hours ago

Saman Gholami

1,87952154

answered 5 hours ago

Saman Gholami

1,87952154

answered 5 hours ago

Saman Gholami

1,87952154

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Htydjtk