how to handle - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to handle

Author

Topic

ajmaly
Starting Member

11 Posts

Posted - 2010-08-25 : 02:41:37

below the 2 different XML that implies I have one or more than one <Transaction> under single <Header>,

XML - 1

DECLARE @StrXML XML
SET @StrXML = '<PDI_Approval_Message>
<Header>
<SourceSystem Value="PDI/Workforce" Name="WF"></SourceSystem>
</Header>
<Transactions>

<Transaction>
<Attributes>
<WF_Regular_Hrs>12</WF_Regular_Hrs>
<WF_Overtime_Hrs>3</WF_Overtime_Hrs>
</Attributes>
</Transaction>

</Transactions>
</PDI_Approval_Message>'
EXEC test @StrXML
XML - 2

DECLARE @StrXML XML
SET @StrXML = '<PDI_Approval_Message>
<Header>
<SourceSystem Value="PDI/Workforce" Name="WF"></SourceSystem>
</Header>
<Transactions>

<Transaction>
<Attributes>
<WF_Regular_Hrs>12</WF_Regular_Hrs>
<WF_Overtime_Hrs>3</WF_Overtime_Hrs>
</Attributes>
</Transaction>

<Transaction>
<Attributes>
<WF_Regular_Hrs>18</WF_Regular_Hrs>
<WF_Overtime_Hrs>4</WF_Overtime_Hrs>
</Attributes>
</Transaction>

</Transactions>
</PDI_Approval_Message>'
EXEC test @StrXML
below SP return 1 row perfectly with XML - 1 and it won't retrieve 2 rows for XML - 2 ,

output for XML - 1,

UID SourceSystemName SourceSystemValue WF_Regular_Hrs WF_Overtime_Hrs

1 WF PDI/Workforce 12 3

I need below output for XML - 2, which is not coming by below SP, what need to do for this???

output for XML - 1,

UID SourceSystemName SourceSystemValue WF_Regular_Hrs WF_Overtime_Hrs

1 WF PDI/Workforce 12 3

2 WF PDI/Workforce 18 4

and here is the SP,

CREATE PROCEDURE test
(
@StrXML XML
)
AS
Begin
DECLARE @vTable TABLE (x XML)
INSERT INTO @vTable(X) SELECT @StrXML

DECLARE @cols NVARCHAR(MAX),
@sql NVARCHAR(MAX)
SET @Cols =
STUFF(
(
SELECT ', MAX(CASE WHEN [Attribute]=' + QUOTENAME(attributes.value('local-name(.)','VARCHAR(25)'),CHAR(39)) +
' THEN [Attribute_Value] ELSE NULL END) AS ' + QUOTENAME(attributes.value('local-name(.)','VARCHAR(25)')) + CHAR(13)
FROM @vTable t
CROSS APPLY @StrXML.nodes('/PDI_Approval_Message/Transactions/Transaction/Attributes/*')Attributes (attributes)
FOR XML PATH(''),TYPE
).value('.','nvarchar(max)'),1,1,'')

SET @sql = N'
DECLARE @vTable TABLE (x XML)
INSERT INTO @vTable(X) SELECT @StrXML

SELECT
ROW_NUMBER() OVER( ORDER BY ( SELECT 1 ) ) AS UID,
SourceSystemName,
SourceSystemValue, ' + CHAR(13) + @cols + '
FROM(
SELECT
header.value(''./SourceSystem[1]/@Name'',''VARCHAR(15)'') AS SourceSystemName,
header.value(''./SourceSystem[1]/@Value'',''VARCHAR(15)'') AS SourceSystemValue,
attributes.value(''local-name(.)'',''VARCHAR(25)'') Attribute,
attributes.value(''.'',''VARCHAR(15)'') AS Attribute_Value
FROM @vTable
CROSS APPLY x.nodes(''/PDI_Approval_Message/Header'')Header(header)
CROSS APPLY x.nodes(''/PDI_Approval_Message/Transactions/Transaction/Attributes/*'')Attributes (attributes)
) AS x
GROUP BY
SourceSystemName,
SourceSystemValue'

DECLARE @parameters NVARCHAR(1000)
SET @parameters = '@StrXML as XML'
EXEC sp_executesql @sql, @parameters, @StrXML
END
GO

ajmal

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-25 : 12:07:45

Dynamic query is an option but should be the last one because of security, performance and maitaince, specially with xml. I am not sure about your real problem, but based on example data and requirement you provided, I think there is a simple way to archive what you want.

below the 2 different XML that implies I have one or more than one <Transaction> under single <Header>,

Run the below script to see if it works for you.

DECLARE @StrXML XML
SET @StrXML = '<PDI_Approval_Message>
<Header>
<SourceSystem Value="PDI/Workforce" Name="WF"></SourceSystem>
</Header>
<Transactions>

<Transaction>
<Attributes>
<WF_Regular_Hrs>12</WF_Regular_Hrs>
<WF_Overtime_Hrs>3</WF_Overtime_Hrs>
</Attributes>
</Transaction>

<Transaction>
<Attributes>
<WF_Regular_Hrs>18</WF_Regular_Hrs>
<WF_Overtime_Hrs>4</WF_Overtime_Hrs>
</Attributes>
</Transaction>
</Transactions>
</PDI_Approval_Message>'

;with temp as
(select
t.c.value('../../../Header[1]/SourceSystem[1]/@Name','varchar(20)') as SourceSystemName,
t.c.value('../../../Header[1]/SourceSystem[1]/@Value','varchar(20)') as SourceSystemValue,
t.c.value('./WF_Regular_Hrs[1]/text()[1]','int') as WF_Regular_Hrs,
t.c.value('./WF_Overtime_Hrs[1]/text()[1]','int') as WF_Overtime_Hrs
from @StrXML.nodes('PDI_Approval_Message/Transactions/Transaction/Attributes') as t(c)
)
select row_number() over(order by SourceSystemName) as [UID], *
from temp

-- result
UID SourceSystemName SourceSystemValue WF_Regular_Hrs WF_Overtime_Hrs
1 WF PDI/Workforce 12 3
2 WF PDI/Workforce 18 4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources