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.

Answer 1

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>