Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Nested XML formatting....

    Why does SQL Server replace "<" and ">" with "&lt;" and "&gt;" in my nested XML query?
    Code:
    set nocount on
    --Set up some tables
    declare	@Container table (ContainerName varchar(15), ContainerColor varchar(15))
    declare	@ContainerBins table (ContainerName varchar(15), ContainerBin int, Contents varchar(5))
    
    --Toss some sample data into them
    insert into @Container values ('TestContainer', 'Red')
    insert into @ContainerBins (ContainerName, ContainerBin, Contents)
    select	ContainerName, ContainerBin, Contents
    from	--DataList
    		(select	'TestContainer' as ContainerName, 1 as ContainerBin, 'A' as Contents
    		union
    		select	'TestContainer' as ContainerName, 2 as ContainerBin, 'B' as Contents
    		union
    		select	'TestContainer' as ContainerName, 3 as ContainerBin, 'C' as Contents
    		union
    		select	'TestContainer' as ContainerName, 4 as ContainerBin, 'D' as Contents) DataList
    
    --This gives the XML parent record correctly:
    select	ContainerName as '@ContainerName',
    		ContainerColor as '@ContainerColor'
    from	@Container
    for XML path ('Container')
    
    --This give the XML detail records correctly:
    select	ContainerBin as '@ContainerBin',
    		Contents as '@Contents'
    from	@ContainerBins
    for XML path ('Bin')
    
    --But combining them mucks up the detail record formatting:
    select	ContainerName as '@ContainerName',
    		ContainerColor as '@ContainerColor',
    		(select	ContainerBin as '@ContainerBin',
    				Contents as '@Contents'
    		from	@ContainerBins
    		for XML path ('Bin'))
    from	@Container
    for XML path ('Container')
    Does this matter, or is it still valid XML? Is there a work around?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Found the answer! Needed to specify "TYPE" in the subquery to return the result as an XML datatype:
    Code:
    select	ContainerName as '@ContainerName',
    		ContainerColor as '@ContainerColor',
    		(select	ContainerBin as '@ContainerBin',
    				Contents as '@Contents'
    		from	@ContainerBins
    		for XML path ('Bin'), type)
    from	@Container
    for XML path ('Container')
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Uh-oh, I thought you said XML is a virus...wait, don't say it...you started writing viruses???
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ah-choo!
    But seriously, I am looking into passing complex data as XML parameters until I can use 2008's new table parameters.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    xml isn't all bad. it has its place.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    I am looking into passing complex data as XML parameters until I can use 2008's new table parameters.
    Interesting. By 'complex' do you mean sets? And are these between sprocs or from FE to BE?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Under SQL 2005, you can use the nodes() method of the XML datatype to return a table like structure:
    Code:
    set quoted_identifier on
    go
    create table testxml
    (col1 xml)
    go
    insert into testxml values ('<l><g id="OP275"/><g id="OP276"/><g id="OP277"/></l>')
    insert into testxml values ('<l><g id="OP275"/></l>')
    go
    
    select a.aa.value('@id', 'varchar(20)') as genericnumber
    from testxml cross apply col1.nodes('/l/g') a(aa)

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Between sprocs. Actully, I am parsing FEN strings, (Forsythe-Edwards Notation), and want to be able to pass the results as "chess position objects" between code blocks. FENs contain six separate elements of data, including the board position. Right now I have a sproc that returns five of the elements as OUTPUT parameters, and the board position as a result set in the form of an 8x8 array. I might be able to use table parameters in 2008, but in the meantime I am seeing if XML would hold any advantages.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and thanks MCrowley. I'll look into the NODES syntax.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I find the nodes() method a bit annoying, since you can not directly see what you are getting from it.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, take that up with MCrowley. He's the one who suggested it. I'll let you two fight it out.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd pay to watch that bout!

    -PatP

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Funny. I have to pay therapists to keep it from happening. No wonder I am poor ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •