Home Forums SQL Server 2008 T-SQL (SS2K8) How do I use one column for node names and the others for elements in that node? RE: How do I use one column for node names and the others for elements in that node?

  • Assuming you fix your naming convention issue (yes - SQL Server will pick up on the invalid name issue), you can use a query like the one below (i fixed the names to make this work)

    declare @x xml

    set @x='<PartsList>

    <a123>

    <Color>blue</Color>

    <Size>small</Size>

    </a123>

    <a124>

    <Color>black</Color>

    <Size>medium</Size>

    </a124>

    <a125>

    <Color>red</Color>

    <Size>large</Size>

    </a125>

    </PartsList>'

    select c.value('local-name(.)','varchar(100)') colname,

    c.value('(./Color)[1]','varchar(100)'),

    c.value('(./Size)[1]','varchar(100)') size

    from @x.nodes('/PartsList/*') vm(c)

    EDIT: sry - I am looking at the wrong end of the issue - you're attempting to generate this - not parse it.

    I am pretty sure this is possible using reasonably advanced FLOQWR statements, but it may take some time to get this right.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?