Build String Based on Data Stored in SQL Server XML Column

Question

I've been attempting to write this query but, so far, to no avail.

The following is some partial data from an XML column in SQL Server:

<DashboardWidgets>
    <DashboardWidget id="14">
        <EnumName>PersonalProgressIndividual</EnumName>
        <OnOff>1</OnOff>
        <Movable>0</Movable>
        <Removable>0</Removable>
        <SubItemData>
            <SubItem id="1">
                <OnOff>1</OnOff>
                <Movable>1</Movable>
                <Removable>1</Removable>
            </SubItem>
            <SubItem id="2">
                <OnOff>1</OnOff>
                <Movable>1</Movable>
                <Removable>1</Removable>
            </SubItem>
            <SubItem id="3">
                <OnOff>1</OnOff>
                <Movable>1</Movable>
                <Removable>1</Removable>
            </SubItem>
            <SubItem id="4">
                <OnOff>0</OnOff>
                <Movable>0</Movable>
                <Removable>0</Removable>
            </SubItem>
            <SubItem id="6">
                <OnOff>0</OnOff>
                <Movable>0</Movable>
                <Removable>0</Removable>
            </SubItem>
        </SubItemData>
    </DashboardWidget>
</DashboardWidgets>

My goal is to query the table and retrieve a formatted string of IDs and values.

For an example, I would need to query the DashboardWidget node with the ID of 14 and build the string from the SubItemData child nodes contained within.

The string result required for a query of the dashboard widget with the ID of 14 would be:

"1,1,1,1|2,1,1,1|3,1,1,1|4,0,0,0|6,0,0,0"

I've been able to come close by extracting all the values but without any delimiters at all.

DECLARE
    @companyID  INT = 23
    ,@dwID      INT = 14
;

DECLARE @xml xml
SELECT @xml = c.DashboardWidgetSettings FROM dbo.Company c WHERE c.CompanyID = @companyID;

SELECT
    x.Rec.query('./SubItem').value('.', 'varchar(max)') AS 'SubItemData'
FROM @xml.nodes('/DashboardWidgets/DashboardWidget[@id=sql:variable("@dwID")]/SubItemData') as x(Rec)
;

Any help or a point in the right direction would be greatly appreciated.

John Cappelletti · Accepted Answer · 2017-04-03 21:46:13Z

1

Declare @XML xml = '<DashboardWidgets><DashboardWidget id="14"><EnumName>PersonalProgressIndividual</EnumName><OnOff>1</OnOff><Movable>0</Movable><Removable>0</Removable><SubItemData><SubItem id="1"><OnOff>1</OnOff><Movable>1</Movable><Removable>1</Removable></SubItem><SubItem id="2"><OnOff>1</OnOff><Movable>1</Movable><Removable>1</Removable></SubItem><SubItem id="3"><OnOff>1</OnOff><Movable>1</Movable><Removable>1</Removable></SubItem><SubItem id="4"><OnOff>0</OnOff><Movable>0</Movable><Removable>0</Removable></SubItem><SubItem id="6"><OnOff>0</OnOff><Movable>0</Movable><Removable>0</Removable></SubItem></SubItemData></DashboardWidget></DashboardWidgets>'

Select Stuff((Select Distinct '|' +String 
              From (
                    Select String = f.n.value('@id','varchar(50)') 
                                   +','
                                   +f.n.value('(OnOff)[1]','varchar(50)') 
                                   +','
                                   +f.n.value('(Movable)[1]','varchar(50)') 
                                   +','
                                   +f.n.value('(Removable)[1]','varchar(50)') 
                     From  @XML.nodes('DashboardWidgets/DashboardWidget/SubItemData') t(n)
                     Cross Apply t.n.nodes('SubItem ') f(n)
                   ) X
              For XML Path ('')),1,1,'')

Returns

1,1,1,1|2,1,1,1|3,1,1,1|4,0,0,0|6,0,0,0

answered Apr 3, 2017 at 21:46

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

webface Over a year ago

Wow! I can't thank you enough, John. That's exactly what I needed and works perfectly! Cheers!

John Cappelletti Over a year ago

@webface Happy to help :)

Collectives™ on Stack Overflow

Build String Based on Data Stored in SQL Server XML Column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related