How I can select XML element from Stored Procedure?

Question

This is output I need to display in XML generate from SQL server:

<Fo>
    <Number>1</Number>
    <CreatedDate>02/06/2016</CreatedDate>
    <CreatedBy>1</CreatedBy>
    <ModifiedDate>02/06/2016</ModifiedDate>
    <ModifiedBy>1</ModifiedBy>,
            <Pro>
                <Number>1</Number>
                <LotNumber>Lot 1288</LotNumber>
            </Pro>
            <Pro>
                <Number>2</Number>
                <LotNumber>Lot 1299</LotNumber>
            </Pro>
            <Pro>
                <Number>3</Number>
                <LotNumber>Lot 1211</LotNumber>
            </Pro>
</Fo>

This is the code I try but still cannot display the result like about.

SELECT *
    (SELECT * FROM TAB_B FOR XML PATH(''))
FROM TBL_A
FOR XML PATH('')

Hi guys, could I get the example script for display the result like about there? <Pro> side is looping result from DB, which means 1 family have many child. Thank You.

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

The comma after "ModifiedBy" seems to be incorrect...

And avoid culture specific date formats!: Your <CreatedDate>02/06/2016</CreatedDate> can be taken as 2nd of June or as 6th of February and might even throw an exception. Within XML use ISO8601 and in SQL you should prefer ODBC, ISO or Universal-Unseparated

Now to your question

This looks like a 1:n-related structure. But there is no hint for a relation (common key).

If this is a 1:n-related structure, I'd assume, that there might be several "Fo" entries with a differing number of Pro-entries. In this case it might be better to encapsulate all "Pro" elements with an own "Pros"-element tag to separate them from the rest:

In my test scenario I added a column "ANumber" to "tblB" as the foreign key and added two rows to "Fo" with child rows in "tblB".

Furthermore I added a general root, which you do not need, if there is just one "fo" element as in your example.

Hint:

If you want to add an encapsulation element to your "Pro" element, just change the inner SELECT to FOR XML PATH('Pro'),ROOT('Pros'),TYPE:

Example

DECLARE @tblA TABLE(Number INT,CreatedDate DATETIME,CreatedBy INT,ModifiedDate DATETIME,ModifiedBy INT);
INSERT INTO @tblA VALUES
 (1,{d'2016-06-02'},1,{d'2016-06-02'},1)
,(2,{d'2016-07-02'},1,{d'2016-07-02'},1);

DECLARE @tblB TABLE(Number INT, ANumber INT,LotNumber VARCHAR(100));
INSERT INTO @tblB VALUES
 (1,1,'Lot 1288')
,(2,1,'Lot 1299')
,(3,1,'Lot 1211')
,(1,2,'Lot 222a')
,(2,2,'Lot 222b');

SELECT tblA.*
      ,(
        SELECT tblB.Number
              ,tblB.LotNumber
        FROM @tblB AS tblB 
        WHERE tblB.ANumber=tblA.Number
        FOR XML PATH('Pro'),TYPE
       )
FROM @tblA AS tblA
FOR XML PATH('Fo'),ROOT('root')

The result

<root>
  <Fo>
    <Number>1</Number>
    <CreatedDate>2016-06-02T00:00:00</CreatedDate>
    <CreatedBy>1</CreatedBy>
    <ModifiedDate>2016-06-02T00:00:00</ModifiedDate>
    <ModifiedBy>1</ModifiedBy>
    <Pro>
      <Number>1</Number>
      <LotNumber>Lot 1288</LotNumber>
    </Pro>
    <Pro>
      <Number>2</Number>
      <LotNumber>Lot 1299</LotNumber>
    </Pro>
    <Pro>
      <Number>3</Number>
      <LotNumber>Lot 1211</LotNumber>
    </Pro>
  </Fo>
  <Fo>
    <Number>2</Number>
    <CreatedDate>2016-07-02T00:00:00</CreatedDate>
    <CreatedBy>1</CreatedBy>
    <ModifiedDate>2016-07-02T00:00:00</ModifiedDate>
    <ModifiedBy>1</ModifiedBy>
    <Pro>
      <Number>1</Number>
      <LotNumber>Lot 222a</LotNumber>
    </Pro>
    <Pro>
      <Number>2</Number>
      <LotNumber>Lot 222b</LotNumber>
    </Pro>
  </Fo>
</root>

Dear @Shnugo: Noted and thanks.. but I have 1 question again about 1 case have many property how I can solve it?? — KH LIM, Aug 04 '16 at 03:41
Hi @KHLIM, one principle on SO is: One issue - one question. If this issue (*How can I select XML element...*) is solved, I ask you to click the upvote and additionally the acceptance check below the vote counter. This will mark this question as solved and brings points to your and my account. Then start a new question and place a link here. I will pop up there soon :-) And please: Try to think as someone who has no idea what you want! Place sample data, show what you've tried and state the expected output. *more than 5 table how can I distinct??* means nothing to me... — Shnugo, Aug 04 '16 at 07:11
Dear @Shnugo: Can i have your email address, EXAMPLE: I have 1 case have many auction, one of auction have more than 1 property.. now I try to distinct foreclosure part so display 2 records.. but in property part I cannot display it.. — KH LIM, Aug 04 '16 at 07:20
Dear @KHLIM, SO is a public platform... Everybody can contribute and profit from all answers. Why do you want to discuss this in private? What against a new question? New questions pull the most attraction! Look at my answer how I declared table variables and filled them with sample data. Try to set up a minimal example this way and what you want to get out of this. Place a **good** question, and you'll have an answer in minutes... — Shnugo, Aug 04 '16 at 07:23
http://stackoverflow.com/questions/38761090/how-to-using-distinct-in-xml-sql-using-stored-procedure — KH LIM, Aug 04 '16 at 07:31
I just scare my question too long, nobody want to answer. I have tried before. sorry i have this experience before. — KH LIM, Aug 04 '16 at 07:33
Dear @Shnugo: I did anew solution. I can distinct between subquery. anywhere Thank You very much.. — KH LIM, Aug 09 '16 at 02:22

How I can select XML element from Stored Procedure?

1 Answers1

Now to your question

Hint:

Example