CODEWITHSUNDEEP
sql-serverxmlt-sqlxpath
Steve
Steve

Reputation: 475

T-SQL to produce XML

We have a table with the column structure of:

And require to output the follow XML:

  <ns0:PhoneNumberList>
    <ns0:PhoneNumber Type="0096">
      <ns0:Number>(08) 8232 5550</ns0:Number>
    </ns0:PhoneNumber>
    <ns0:PhoneNumber Type="0026">
      <ns0:Number>(08) 8336 1050</ns0:Number>
    </ns0:PhoneNumber>
  </ns0:PhoneNumberList>

For the main phone so far I have:

WITH xmlnamespaces ('http://www.sifassociation.org/datamodel/au/3.4' AS ns0)
SELECT  si.MainPhoneTypeCode AS [@Type]
       ,si.MainPhoneNumber   AS [ns0:Number]
FROM    EDU.tbl_EDU_SchoolInfo si
WHERE   si.SchoolInfoID = 4
FOR XML PATH('ns0:PhoneNumber'), TYPE, ROOT('ns0:PhoneNumberList')

Which generates:

<ns0:PhoneNumberList xmlns:ns0="http://www.sifassociation.org/datamodel/au/3.4">
  <ns0:PhoneNumber Type="0096">
    <ns0:Number>(08) 8232 5550</ns0:Number>
  </ns0:PhoneNumber>
</ns0:PhoneNumberList>

But unsure how to add the alternative number to be included in the PhoneNumberList element.

Upvotes: 0

Views: 41

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22321

There is a need for a namespace declaration because all XML tags have a namespace prefix in them.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE 
(
    SchoolInfoID  INT PRIMARY KEY
    , MainPhoneTypeCode CHAR(4)
    , MainPhoneNumber VARCHAR(20)
    , AlternateNumberTypeCode CHAR(4)
    , AlternatePhoneNumber VARCHAR(20)
);
INSERT INTO @tbl (
    SchoolInfoID,
    MainPhoneTypeCode,
    MainPhoneNumber,
    AlternateNumberTypeCode,
    AlternatePhoneNumber
)
VALUES
(4, '0096', '(08) 8232 5550', '0026', '(08) 8336 1050');
-- DDL and sample data population, end

;WITH XMLNAMESPACES ('http://www.sifassociation.org/datamodel/au/3.4' AS ns0)
SELECT  MainPhoneTypeCode AS [ns0:PhoneNumber/@Type]
       , MainPhoneNumber   AS [ns0:PhoneNumber/ns0:Number]
       , ''
       , AlternateNumberTypeCode AS [ns0:PhoneNumber/@Type]
       , AlternatePhoneNumber   AS [ns0:PhoneNumber/ns0:Number]
FROM @tbl
WHERE SchoolInfoID = 4
FOR XML PATH(''), TYPE, ROOT('ns0:PhoneNumberList');

Output

<ns0:PhoneNumberList xmlns:ns0="http://www.sifassociation.org/datamodel/au/3.4">
  <ns0:PhoneNumber Type="0096">
    <ns0:Number>(08) 8232 5550</ns0:Number>
  </ns0:PhoneNumber>
  <ns0:PhoneNumber Type="0026">
    <ns0:Number>(08) 8336 1050</ns0:Number>
  </ns0:PhoneNumber>
</ns0:PhoneNumberList>

Upvotes: 1

Related Questions