Generate complex XML with T-SQL syntax

Question

I am trying to build a query that would return more or less complex XML structure. This is expected output I would like to have:

<s:Envelope xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope">
    <s:Header>
        <a:Action s:mustUnderstand="1">http://tempuri.org/IGeocodeService/HereRouteMatchExtension</a:Action>
        <a:MessageID>urn:uuid: some_messageID</a:MessageID>
        <a:ReplyTo>
            <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>
        </a:ReplyTo>
        <a:To s:mustUnderstand="1">ServiceURL</a:To>
    </s:Header>
    <s:Body>
                 <HereRouteMatchExtension xmlns="http://tempuri.org/">
                       <vehicleTrace xmlns:b="http://schemas.datacontract.org/2004/07/FMCommonTypes.WCF" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                                  <s:Latitude>2</s:Latitude>
                                  <s:Longitude>2</s:Longitude>
                                  <s:PositionGuid>577AF773-C7A8-4D65-82DA-37A15CC7611D</s:PositionGuid>
                       </vehicleTrace>
                 </HereRouteMatchExtension>
    </s:Body>
</s:Envelope>

I am using following code:

CREATE TABLE #test
(
    Latitude INT,
    Longitude INT, 
    PositionGuid UNIQUEIDENTIFIER
)

INSERT INTO #test VALUES (1,1,NEWID())
INSERT INTO #test VALUES (2,2,NEWID())



WITH XMLNAMESPACES ('http://www.w3.org/2003/05/soap-envelope' AS s, 'http://www.w3.org/2005/08/addressing' AS a)
SELECT
( SELECT '1' AS  [a:Action/@mustUnderstand], 
 'http://tempuri.org/IGeocodeService/HereRouteMatchExtension' AS [a:Action]
  FOR XML PATH (''), TYPE
),
'urn:uuid: some_messageID' AS 'a:MessageID',
( SELECT  'http://www.w3.org/2005/08/addressing/anonymous' AS [a:Address]
  FOR XML PATH ('a:ReplyTo'), TYPE
),
( SELECT '1' AS  [a:To/@mustUnderstand], 
 'ServiceURL' AS [a:To]
  FOR XML PATH (''), TYPE
),
( SELECT '1' AS  [a:Action/@mustUnderstand]
FOR XML PATH (''), TYPE

),
(SELECT Latitude AS 's:Latitude',
       Longitude      AS 's:Longitude', 
       PositionGuid     AS 's:PositionGuid'
FROM #test
FOR XML PATH ('s:Body'),  TYPE
)

FOR XML RAW ('s:Header'), ELEMENTS, ROOT('s:Envelope')

There are 2 problems in the code i generated:

1) reference URLS are in every sub-section and I would like to have just once;

2) Body tag is inside the header and it is supposed to go straight after it...

How can I achieve that? This is the result I am getting:

<s:Envelope xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope">
  <s:Header>
    <a:Action xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope" mustUnderstand="1">http://tempuri.org/IGeocodeService/HereRouteMatchExtension</a:Action>
    <a:MessageID>urn:uuid: some_messageID</a:MessageID>
    <a:ReplyTo xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope">
      <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>
    </a:ReplyTo>
    <a:To xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope" mustUnderstand="1">ServiceURL</a:To>
    <a:Action xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope" mustUnderstand="1" />
    <s:Body xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope">
      <s:Latitude>1</s:Latitude>
      <s:Longitude>1</s:Longitude>
      <s:PositionGuid>34BD8E91-8567-4D58-A18E-61C3FBBF5C8F</s:PositionGuid>
    </s:Body>
  </s:Header>
</s:Envelope>

By "reference URLS", do you mean the xmlns:a etc namespace declarations? — IMSoP
– IMSoP, Commented Apr 27, 2015 at 19:53

Stavros Zotalis · Accepted Answer · 2015-04-27 21:38:11Z

2

Try that

DECLARE  @test TABLE
(
    Latitude INT,
    Longitude INT, 
    PositionGuid UNIQUEIDENTIFIER
)

INSERT INTO @test VALUES (1,1,NEWID());
INSERT INTO @test VALUES (2,2,NEWID());

    WITH XMLNAMESPACES ('http://www.w3.org/2003/05/soap-envelope' AS s, 'http://www.w3.org/2005/08/addressing' AS a)
    SELECT
        1 AS [s:Envelope/s:Header/a:Action/@s:mustUnderstand],
        'http://tempuri.org/IGeocodeService/HereRouteMatchExtension' AS [s:Envelope/s:Header/a:Action],
        'urn:uuid: some_messageID' AS [s:Envelope/s:Header/a:MessageID],
        'http://www.w3.org/2005/08/addressing/anonymous' [s:Envelope/s:Header/a:ReplyTo/a:Address],
        1  as [s:Envelope/s:Header/To/@s:mustUnderstand],
        'ServiceURL' as  [s:Envelope/s:Header/To],
        Latitude as [s:Envelope/s:Body/s:Latitude],
        Longitude as  [s:Envelope/s:Body/s:Longitude],
        PositionGuid as  [s:Envelope/s:Body/s:PositionGuid]
        FROM @test
        FOR XML PATH('')

This way you produce two Envelope elements one for each row of the @table.

Edit:

I could not find a way to remove extra namespaces from child elements when generated with TYPE in a subquery with FOR XML PATH.

I came up with this solution which does not fully satisfy me:

DECLARE  @test TABLE
(
    Latitude INT,
    Longitude INT, 
    PositionGuid UNIQUEIDENTIFIER,
    x xml
)



DECLARE @x xml = 
'<s:Envelope xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope">
    <s:Header>
        <a:Action s:mustUnderstand="1">http://tempuri.org/IGeocodeService/HereRouteMatchExtension</a:Action>
        <a:MessageID>urn:uuid: some_messageID</a:MessageID>
        <a:ReplyTo>
            <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>
        </a:ReplyTo>
        <a:To s:mustUnderstand="1">ServiceURL</a:To>
    </s:Header>
    <s:Body>
                <HereRouteMatchExtension xmlns="http://tempuri.org/">
                       <vehicleTrace xmlns:b="http://schemas.datacontract.org/2004/07/FMCommonTypes.WCF" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                                  <s:Latitude>2</s:Latitude>
                                  <s:Longitude>2</s:Longitude>
                                  <s:PositionGuid>577AF773-C7A8-4D65-82DA-37A15CC7611D</s:PositionGuid>
                       </vehicleTrace>
                 </HereRouteMatchExtension>
    </s:Body>
</s:Envelope>'

INSERT INTO @test VALUES (10,10,NEWID(),@x);
INSERT INTO @test VALUES (20,20,NEWID(),@x);


UPDATE @test
SET x.modify('declare namespace s="http://www.w3.org/2003/05/soap-envelope";declare default element  namespace "http://tempuri.org/";
replace value of (/s:Envelope/s:Body/HereRouteMatchExtension/vehicleTrace/s:Latitude/text())[1] with sql:column("Latitude")')
UPDATE @test
SET x.modify('declare namespace s="http://www.w3.org/2003/05/soap-envelope";declare default element  namespace "http://tempuri.org/";
replace value of (/s:Envelope/s:Body/HereRouteMatchExtension/vehicleTrace/s:Longitude/text())[1] with sql:column("Longitude")')
UPDATE @test
SET x.modify('declare namespace s="http://www.w3.org/2003/05/soap-envelope";declare default element  namespace "http://tempuri.org/";
replace value of (/s:Envelope/s:Body/HereRouteMatchExtension/vehicleTrace/s:PositionGuid/text())[1] with sql:column("PositionGuid")')


SELECT * FROM @test

and all at once with an insert:

DECLARE  @test TABLE
(
    Latitude INT,
    Longitude INT, 
    PositionGuid UNIQUEIDENTIFIER,
    x xml
)



DECLARE @x xml = 
'<s:Envelope xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope">
    <s:Header>
        <a:Action s:mustUnderstand="1">http://tempuri.org/IGeocodeService/HereRouteMatchExtension</a:Action>
        <a:MessageID>urn:uuid: some_messageID</a:MessageID>
        <a:ReplyTo>
            <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>
        </a:ReplyTo>
        <a:To s:mustUnderstand="1">ServiceURL</a:To>
    </s:Header>
    <s:Body>
                <HereRouteMatchExtension xmlns="http://tempuri.org/">
                 </HereRouteMatchExtension>
    </s:Body>
</s:Envelope>'

INSERT INTO @test VALUES (10,10,NEWID(),@x);
INSERT INTO @test VALUES (20,20,NEWID(),@x);

UPDATE @test
SET x.modify('declare namespace s="http://www.w3.org/2003/05/soap-envelope";declare default element  namespace "http://tempuri.org/";
insert  <vehicleTrace xmlns:b="http://schemas.datacontract.org/2004/07/FMCommonTypes.WCF" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><s:Latitude>{sql:column("Latitude")}</s:Latitude>
        <s:Longitude>{sql:column("Longitude")}</s:Longitude>
        <s:PositionGuid>{sql:column("PositionGuid")}</s:PositionGuid></vehicleTrace> into (/s:Envelope/s:Body/HereRouteMatchExtension)[1]')


SELECT * FROM @test

edited Apr 27, 2015 at 21:38

answered Apr 27, 2015 at 19:54

Stavros Zotalis

7264 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Dmitrij Kultasev Over a year ago

Thanks. That's exactly what I asked. However I modified the question a bit, could you please take a look to the <body>...</body>. I've changed the code a bit there.

Stavros Zotalis Over a year ago

I think it can also be done with one insert instead of three replaces

Stavros Zotalis Over a year ago

With the second and third snippets of code it is not generated. It is already there. In the first occasion the code just replaces the values of the Longitude, Latitude and PositionGuid elemets. In the second, it inserts a constructed vehicleTrace element.

Dmitrij Kultasev Over a year ago

what I mean is that I modified the question little bit (inside <body> tag). Could you please show me how to do that?

Stavros Zotalis Over a year ago

the second and third are based on the modified question did you tried to run them?

|

Collectives™ on Stack Overflow

Generate complex XML with T-SQL syntax

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related