August 31, 2012 at 5:45 am
Hi to all,
I have the follwing piece of TSQL COde
SET @DateTo = '20110330'
SELECT
----- FileHeader ------------------------------------------------------------------
CONVERT(VARCHAR(16), GETDATE(), 120) AS '@Created'
,'Nephro, Indication' AS '@Software'
,'7.6.31.1577' AS '@SoftwareVer'
,'V3.0_DN' AS '@Specification'
------- SenderPackage ---------------------------------------------------------------
,(SELECT @CenterNumber AS '@CenterNumber'
,'1111' AS '@ContractDoctorNumber'
,CONVERT(VARCHAR(10), @DateFrom, 120) AS '@DateFrom'
,CONVERT(VARCHAR(10), @DateTo, 120) AS '@DateTo'
FOR
XML PATH('SenderPackage'), -- The element name for each row.
TYPE
)
----------------------------------------------------------------------------------
FOR XML PATH('FileHeader'),
ROOT('QuasyNeTPackage')
My problem is that I want to have the SenderPackage information on the same level like the FileHeader.
At the moment the SenderPackage information is included into the FileHeader tag.
Can someone help me and correct my code.
Thank you in advance
September 2, 2012 at 7:15 pm
Like this perhaps?
DECLARE @DateTo DATETIME = '20110330', @DateFROM DATETIME = '20100330'
,@CenterNumber INT = 200
SELECT
----- FileHeader ------------------------------------------------------------------
(SELECT
CONVERT(VARCHAR(16), GETDATE(), 120) AS '@Created'
,'Nephro, Indication' AS '@Software'
,'7.6.31.1577' AS '@SoftwareVer'
,'V3.0_DN' AS '@Specification'
FOR XML PATH('FileHeader'), TYPE)
------- SenderPackage ---------------------------------------------------------------
,(SELECT @CenterNumber AS '@CenterNumber'
,'1111' AS '@ContractDoctorNumber'
,CONVERT(VARCHAR(10), @DateFrom, 120) AS '@DateFrom'
,CONVERT(VARCHAR(10), @DateTo, 120) AS '@DateTo'
FOR
XML PATH('SenderPackage'), -- The element name for each row.
TYPE
)
----------------------------------------------------------------------------------
FROM (SELECT NULL) a(x)
FOR XML PATH(''),
ROOT('QuasyNeTPackage')
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply