Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML Question (?) Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 6:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 66, Visits: 361
Hi,

Let's say I have the following table with just 1 record:

SELECT Field1, Field2, Field3 FROM tbl

returns 11, 22, 33

What would be a query to return something like:

<CustomInformation>
<ColumnValue name="Field1">11</ColumnValue>
<ColumnValue name="Field2">22</ColumnValue>
<ColumnValue name="Field3">33</ColumnValue>
</CustomInformation>

?

Thanks!

Post #1444201
Posted Friday, April 19, 2013 2:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555

SELECT 
(SELECT 'Field1' AS "@name", Field1 AS "text()" FOR XML PATH('ColumnValue'),TYPE),
(SELECT 'Field2' AS "@name", Field2 AS "text()" FOR XML PATH('ColumnValue'),TYPE),
(SELECT 'Field3' AS "@name", Field3 AS "text()" FOR XML PATH('ColumnValue'),TYPE)
FROM tbl
FOR XML PATH('CustomInformation')



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1444276
Posted Friday, April 19, 2013 11:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 66, Visits: 361
Beautiful, thanks so much!
Post #1444542
Posted Tuesday, April 30, 2013 6:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 66, Visits: 361
Journey goes on...

Now, what if I have two records and need something like

<instances>

<instance instanceCode1="Rec1_Code1" instanceCode2="Rec1_Code2">
....<CustomInformation>
........<ColumnValue name="Field1">11</ColumnValue>
........<ColumnValue name="Field2">22</ColumnValue>
........<ColumnValue name="Field3">33</ColumnValue>
....</CustomInformation>
</instance>

<instance instanceCode1="Rec2_Code1" instanceCode2="Rec2_Code2">
....<CustomInformation>
........<ColumnValue name="Field1">1111</ColumnValue>
........<ColumnValue name="Field2">2222</ColumnValue>
........<ColumnValue name="Field3">3333</ColumnValue>
....</CustomInformation>
</instance>

</instances>

?

I figured the external <instances>, the internal <instance>.... Totally stuck

Thanks in advance as always
Post #1448280
Posted Wednesday, May 1, 2013 2:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555

Not sure of your table structure, but this give the correct results


DECLARE @tbl TABLE(Code1 VARCHAR(10), Code2 VARCHAR(10), Field1 INT, Field2 INT, Field3 INT)
INSERT INTO @tbl(Code1,Code2,Field1,Field2,Field3)
VALUES ('Rec1_Code1','Rec1_Code2',11,22,33),
('Rec2_Code1','Rec2_Code2',1111,2222,3333);

SELECT
Code1 AS "@instanceCode1",
Code2 AS "@instanceCode2",
(SELECT
(SELECT 'Field1' AS "@name", Field1 AS "text()" FOR XML PATH('ColumnValue'),TYPE),
(SELECT 'Field2' AS "@name", Field2 AS "text()" FOR XML PATH('ColumnValue'),TYPE),
(SELECT 'Field3' AS "@name", Field3 AS "text()" FOR XML PATH('ColumnValue'),TYPE)
FOR XML PATH('CustomInformation'),TYPE)
FROM @tbl
FOR XML PATH('instance'),Root('instances');



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1448338
Posted Wednesday, May 1, 2013 12:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 66, Visits: 361
Ok, getting there

works fine, thank you once again
Post #1448570
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse