Question create XML from SQL for XML format

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

Ok, we know your requirement. Now what is your question?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Con Alexis Old Hand Points: 331 More actions · Answer 2

This is one way of doing it.

DECLARE @Customers TABLE ( Col1 varchar(50), Col2 varchar(50), Col3 varchar(50) )

INSERT INTO @Customers ( Col1, Col2, Col3 )

VALUES ( 'Value from Col1 in Table Customers', 'Value from Col2 in Table Customers', 'Value from Col3 in Table Customers' )

SELECT

'myname' AS [TopHeader/Sensor/@user],

'mypassword' AS [TopHeader/Sensor/@pw],

(

SELECT

'Change' AS [@TypeOfRequest],

(

SELECT X.ATT AS [@ATT], X.ColVal AS [@ATTV]

FROM

(

SELECT 'StaticV1' AS ATT, Col1 AS ColVal FROM @Customers

UNION ALL SELECT 'StaticV2' AS ATT, Col2 FROM @Customers

UNION ALL SELECT 'StaticV3' AS ATT, Col3 FROM @Customers

) X

FOR XML PATH('NewParameter'), TYPE

)

FOR XML PATH('NewRequest'), ROOT('Main'), TYPE

)

FOR XML PATH('ALLRequests'), TYPE

fafful SSC Eights! Points: 810 More actions · Answer 3

Thank you it works but how do i include the

<?xml version="1.0" ?>

so that i can pass this on as a XML parameter to a stored procedure.

I appreciate your help in the matter

regards

fafful SSC Eights! Points: 810 More actions · Answer 4

Thank you for the response.

I forgot to add this specific:

I need the XML declaration on top of XML String as well.

I also want to leave out the element NewParameter for row/record where Col4 value is Null or empty

</NewRequest>

Thanks