February 22, 2012 at 2:57 am
Good Morning/Evening!
Just having one of those problems that I know is a doddle to fix but I cannot find the answer. I am executing a very, very simple FOR XML query. Everything is fine until I try to do the following
<PEOPLE>
<Person role="SUPPLIER">
<Name>Some persons name>
I have the select...''as PEOPLE But I cant seem to create a new Person Role with a value and keep it in the structure above.
I am sure I have done it previously but compeltely blank.
As always any help is appreciated!
'Only he who wanders finds new paths'
February 22, 2012 at 3:12 am
I’m sorry, but I can’t understand what you need. Can you post a small script that creates a table, inserts few records into the table and then show us what the XML should look like? If you’ll do that, you’ll get better help and faster.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 22, 2012 at 3:18 am
Essentially, if you use my brief example above then I need to create an Attribute inside some XML. So <Person role="Supplier"> - then some elements under person then, </Person>. Thats it!
'Only he who wanders finds new paths'
February 22, 2012 at 3:29 am
Is this is what you need?
select
'SUPPLIER' as 'PEOPLE/Person/@role',
'Some persons' as 'PEOPLE/Name'
for xml path('')
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 22, 2012 at 3:36 am
Kind of, many thanks but I would still need an end tag for Person..
I am getting the following error as the actual query is a little more complex Attribute-centric column ‘@role’ must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML, but I think I can work that out.
'Only he who wanders finds new paths'
February 22, 2012 at 3:40 am
No its OK, I left the element in above the attribute line, removing that and hey presto! End tag included. Thank you ever so much, massively appreciated.
However, what if I wanted to do the following
<PERSON Role="SUPPLIER">
<Name="Some Name1"
</PERSON>
<PERSON Role="Buyer">
<Name="Some Name2"
</PERSON>
'Only he who wanders finds new paths'
February 22, 2012 at 5:08 am
david.alcock (2/22/2012)
No its OK, I left the element in above the attribute line, removing that and hey presto! End tag included. Thank you ever so much, massively appreciated.However, what if I wanted to do the following
<PERSON Role="SUPPLIER">
<Name="Some Name1"
</PERSON>
<PERSON Role="Buyer">
<Name="Some Name2"
</PERSON>
Post your DDL and sample script next time and you'll get a more complete answer.
SELECT a.roles AS 'Person/@role', a.NAME AS 'Person/Name'
FROM (VALUES ('SUPPLIER', 'Some Name1'), ('Buyer', 'Some Name2')) a(roles, NAME)
FOR XML path('')
Returns: -
<Person role="SUPPLIER">
<Name>Some Name1</Name>
</Person>
<Person role="Buyer">
<Name>Some Name2</Name>
</Person>
February 22, 2012 at 5:24 am
Many thanks, I was trying to simplify the script, appreciate a more concise example would have been a bit more helpful. Is there a way, like having the Person element with an attribute and a value as well. So it would look like:
<Person role="SUPPLIER">Persons Role</Person role>
This is what I have:
'' AS 'Document/Parties/Party/Branch',
'' AS 'Document/Parties/Party/Branch/Address',
'street' AS 'Document/Parties/Party/Branch/Address/AddressLine/@type',
'' AS 'Document/Parties/Party/Branch/Address',
'town' AS 'Document/Parties/Party/Branch/Address/AddressLine/@type'
But I am trying to add a value to the street and town attributes in the element.
'Only he who wanders finds new paths'
February 22, 2012 at 6:11 am
SELECT a.roles AS 'PEOPLE/Person/@role', a.personrole AS 'PEOPLE/Person', a.NAME AS 'PEOPLE/Name'
FROM (VALUES ('SUPPLIER', 'Another Thing', 'Some Name1'), ('Buyer', 'Another Thing2', 'Some Name2')) a(roles, personrole, NAME)
FOR XML path('')
Returns
<PEOPLE>
<Person role="SUPPLIER">Another Thing</Person>
<Name>Some Name1</Name>
</PEOPLE>
<PEOPLE>
<Person role="Buyer">Another Thing2</Person>
<Name>Some Name2</Name>
</PEOPLE>
February 22, 2012 at 6:22 am
david.alcock (2/22/2012)
Many thanks, I was trying to simplify the script, appreciate a more concise example would have been a bit more helpful. Is there a way, like having the Person element with an attribute and a value as well. So it would look like:<Person role="SUPPLIER">Persons Role</Person role>
This is what I have:
'' AS 'Document/Parties/Party/Branch',
'' AS 'Document/Parties/Party/Branch/Address',
'street' AS 'Document/Parties/Party/Branch/Address/AddressLine/@type',
'' AS 'Document/Parties/Party/Branch/Address',
'town' AS 'Document/Parties/Party/Branch/Address/AddressLine/@type'
But I am trying to add a value to the street and town attributes in the element.
Any reason that you insist on not writing the small script that both me and Cadavre asked for? If you’ll write this small script and show us what you need, you’ll get much better help.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 22, 2012 at 6:23 am
Thank you ever so much, greatly appreciated.
'Only he who wanders finds new paths'
February 22, 2012 at 7:31 am
Adi Cohn
I did say that I appreciated a more concise script would be helpful but the full script is a mess really and I was only after pointers in the right direction (not a straight solution) which you both provided, I am a big believer in trying things out myself as opposed to copy and paste...certainly in this example.
Just to point out, I did post a note just to show how much I appreciated the assistance from both of you.
'Only he who wanders finds new paths'
February 22, 2012 at 9:24 am
Hi David
My remark was not meant to make feel bad. You have to understand that using a script and showing us what you need, helps most of us getting the answer faster. I agree with you that copy paste from this site is not the way to learn something, but when I ask for a script, I want a small script that gives us enough data about what you need. For example – I know that your table includes more then 2 columns, but a small script like the one bellow would have helped me and others to answer the question:
create table MyTable (Role varchar(20), Name varchar(20))
go
insert into MyTable (Role, Name)
select 'SUPPLIER', 'SomeName1' union
select 'Buyer', 'Some Name2'
go
/*
please show me how to get this XML:
<Person role="SUPPLIER">
<Name>SomeName1</Name>
</Person>
<Person role="Buyer">
<Name>Some Name2</Name>
</Person>
*/
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 22, 2012 at 10:21 am
Fully appreciate and understand why the scripts are handy. It was just very difficult to put in the full script and as stated (if I hacked bits out possibly it wouldnt have made any sense).
As said I didnt just want the solution just pointers in the right direction (I am admin more development) and all given were fantastic and helped me solve the problem, alls well that ends well. Of course the daft thing is I was trying to simplify for you guys!
Once again, thank you ever so much for the assistance, hats off to you 🙂
'Only he who wanders finds new paths'
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply