January 31, 2011 at 8:29 am
I am trying to pivot the result of the query and then want to insert into table.
Below is the code and the result set I am trying to do.
/*result
key Value
property1value1
property2value2
property3value3
property4value4
*/
instead of I need the Below result set.
property1 property2 property3 property4
value1 value2 value3 value4
DECLARE @x XML
SELECT @x='<MoreProperties>
<Property>
<Key>property1</Key>
<Value>value1</Value>
</Property>
<Property>
<Key>property2</Key>
<Value>value2</Value>
</Property>
<Property>
<Key>property3</Key>
<Value>value3</Value>
</Property>
<Property>
<Key>property4</Key>
<Value>value4</Value>
</Property>
</MoreProperties>'
select
n.value('Key[1]','varchar(30)') as [Key],
n.value('Value[1]','varchar(30)') as [Value]
FROM @x.nodes('MoreProperties/Property') as a(n)
January 31, 2011 at 9:10 am
This should work. See the link in my signature on Crosstabs/pivots for additional information on how this works.
DECLARE @x xml
SELECT @x='<MoreProperties>
<Property>
<Key>property1</Key>
<Value>value1</Value>
</Property>
<Property>
<Key>property2</Key>
<Value>value2</Value>
</Property>
<Property>
<Key>property3</Key>
<Value>value3</Value>
</Property>
<Property>
<Key>property4</Key>
<Value>value4</Value>
</Property>
</MoreProperties>'
;WITH A AS (
select
n.value('Key[1]','varchar(30)') as [Key],
n.value('Value[1]','varchar(30)') as [Value]
FROM @x.nodes('MoreProperties/Property') as a(n))
SELECTMAX(CASE WHEN [Key] = 'Property1' THEN Value ELSE NULL END) Property1,
MAX(CASE WHEN [Key] = 'Property2' THEN Value ELSE NULL END) Property2,
MAX(CASE WHEN [Key] = 'Property3' THEN Value ELSE NULL END) Property3,
MAX(CASE WHEN [Key] = 'Property4' THEN Value ELSE NULL END) Property4
-- INTO Sometable
FROM A
January 31, 2011 at 10:12 am
Garadin,
I appriciate your fast responce.
But in my actual xml file i have name space so in the code i am declaring the name space.
;WITH XMLNAMESPACES(default 'http://schemas.xmlaaaa.org/aaaaenv/envelope/')
so when i am trying to do the using cte its giving me syntex error.
I am able to achive the output using the case statement but i think ur solution using the cte is better. If is there any way that i can use cte with namespace.
January 31, 2011 at 12:51 pm
krishusavalia (1/31/2011)
Garadin,I appriciate your fast responce.
But in my actual xml file i have name space so in the code i am declaring the name space.
;WITH XMLNAMESPACES(default 'http://schemas.xmlaaaa.org/aaaaenv/envelope/')
so when i am trying to do the using cte its giving me syntex error.
I am able to achive the output using the case statement but i think ur solution using the cte is better. If is there any way that i can use cte with namespace.
I've never tried this, but the documentation says that the NAMESPACE must appear before any CTEs, and the WITH only appears once, the rest use commas, so the following should work:
;WITH XMLNAMESPACES(default 'http://schemas.xmlaaaa.org/aaaaenv/envelope/')
, CTE1 AS ( <CTE definition here> )
, CTE2 AS ( <CTE2 definition here> )
, etc., etc.,
SELECT <fields>
FROM <tables>
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 2, 2011 at 9:18 am
Below is the Code What i am trying to do.
I am stuck in the parsing the identity value of the parents to child node.
I know that i am doing something wrong into giving correct path for the XML query but i tried most of the things.
And have moreproperties, have a table structue but stucj into pivot that with identity value of the child table.
/*
create table #parentpackets
(
ParentPacketsID int identity(1,1),
ParentID int,
effectivedate date
)
create table #child
(
ChildID int identity(1,1),
ParentPacketsID int,
[Type] varchar(10),
FirstName varchar(10),
LastName varchar(10),
SSN int
)
Create table #MoreProperties
(
MorepropertiesID int identity(1,1),
ChildID int,
Property1 varchar(10),
Property2 varchar(10),
Property3 varchar(10),
Property4 varchar(10)
)
*/
declare @x xml
SELECT @x='<ParentPackets>
<ParentPacket>
<ParentID>123456</ParentID>
<EffectiveDate>2010-01-05</EffectiveDate>
<Child type="Child">
<FirstName>tommy</FirstName>
<LastName>devda</LastName>
<SSN>145256653</SSN>
<MoreProperties>
<Property>
<Key>property1</Key>
<Value>value1</Value>
</Property>
<Property>
<Key>property2</Key>
<Value>value2</Value>
</Property>
<Property>
<Key>property3</Key>
<Value>value3</Value>
</Property>
<Property>
<Key>property4</Key>
<Value>value4</Value>
</Property>
</MoreProperties>
</Child>
</ParentPacket>
<ParentPacket>
<ParentID>454656</ParentID>
<EffectiveDate>2010-01-06</EffectiveDate>
<Child type="Child">
<FirstName>mone</FirstName>
<LastName>desmond</LastName>
<SSN>156541155</SSN>
<MoreProperties>
<Property>
<Key>property1</Key>
<Value>value11</Value>
</Property>
<Property>
<Key>property2</Key>
<Value>value22</Value>
</Property>
<Property>
<Key>property3</Key>
<Value>value33</Value>
</Property>
<Property>
<Key>property4</Key>
<Value>value44</Value>
</Property>
</MoreProperties>
</Child>
</ParentPacket>
<ParentPacket>
<ParentID>1424555</ParentID>
<EffectiveDate>2010-01-07</EffectiveDate>
<Child type="Child">
<FirstName>gopmmy</FirstName>
<LastName>sephard</LastName>
<SSN>444526689</SSN>
<MoreProperties>
<Property>
<Key>property1</Key>
<Value>value111</Value>
</Property>
<Property>
<Key>property2</Key>
<Value>value222</Value>
</Property>
<Property>
<Key>property3</Key>
<Value>value333</Value>
</Property>
<Property>
<Key>property4</Key>
<Value>value444</Value>
</Property>
</MoreProperties>
</Child>
</ParentPacket>
</ParentPackets>'
declare @parentID table (ParentpacketsID int,ParentID int)
insert into #parentpackets
output inserted.ParentPacketsID,inserted.ParentID
into @parentID
select
n.value('ParentID[1]','INT') as ParentID,
n.value('EffectiveDate[1]','date')as EffectiveDate
from @x.nodes('ParentPackets/ParentPacket') m(n)
insert into #child
select
a.ParentpacketsID,
m.value('@type[1]','varchar(10)') as type,
m.value ('FirstName[1]','varchar(10)')as FirstName,
m.value('LastName[1]','varchar(10)')as LastName,
m.value('SSN[1]','int')as SSN
from @x.nodes('ParentPackets/ParentPacket') m(n)
cross apply m.n.nodes('Child') l(m)
cross apply @parentID as a
where a.ParentID=n.value('@ParentID[1]','int')
/*
select * from #parentpackets
select * from #child
select * from #MoreProperties
drop table #parentpackets
drop table #child
drop table #MoreProperties
*/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply