Convert data from User-defined table type to XML

Question

I have user-defined table type:

    CREATE TYPE [dbo].[t_data] AS TABLE(
      [nID] [numeric](20, 0) NOT NULL,
      [nLocationKey] [numeric](20, 0) NULL,
      [nMTkey] [numeric](20, 0) NULL
    )

Need to get XML like:

  <?xml version="1.0" encoding="utf-8"?>
  <LISTSAVE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
       <LIST>
            <ITEM>
                 <NMTKEY>1231</NMTKEY>
                 <NLOCATIONKEY>1123</NLOCATIONKEY>
            </ITEM>
            <ITEM>
                 <NMTKEY>7778</NMTKEY>
                 <NLOCATIONKEY>9999</NLOCATIONKEY>
            </ITEM>
       </LIST>
  </LISTSAVE>

I want to make function that will make xml from this data like:

   CREATE FUNCTION [LOC_API].[wrapLocationData] (
       @in_oData dbo.t_data READONLY
       )
       RETURNS XML
       AS
    BEGIN
    DECLARE @l_cData XML
    /* Data to XML conversion */
    RETURN @l_cData
    END

Answer 1

The XML query is no different because you use a table type.

CREATE FUNCTION [dbo].[wrapLocationData]
(
  @in_oData dbo.t_data READONLY
) RETURNS XML AS
BEGIN
  DECLARE @l_cData XML;

  WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/2001/XMLSchema',
                     'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
  SELECT @l_cData = (
                    SELECT (
                           SELECT D.nMTkey AS NMTKEY,
                                  D.nLocationKey AS NLOCATIONKEY
                           FROM @in_oData AS D
                           FOR XML PATH('ITEM'), ROOT('LIST'), TYPE
                           )
                    FOR XML PATH('LISTSAVE'), TYPE
                    );

  RETURN @l_cData;
END

Result:

<LISTSAVE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.w3.org/2001/XMLSchema">
  <LIST xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.w3.org/2001/XMLSchema">
    <ITEM>
      <NMTKEY>3</NMTKEY>
      <NLOCATIONKEY>2</NLOCATIONKEY>
    </ITEM>
    <ITEM>
      <NMTKEY>6</NMTKEY>
      <NLOCATIONKEY>5</NLOCATIONKEY>
    </ITEM>
  </LIST>
</LISTSAVE>

There is to my knowledge no sane way to avoid the double namespace declarations. See How to remove xmlns from child elements with FOR XML