Reputation: 475
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
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