XML parsing using SQL

Question

How i can parse the following XML to get output like below name = Business, Value = XYZ name = Product, Value = STANDARD name = Trend, Value = Active,Active New,Sparse etc Business,Product,Trend data can be dynamic,some xml do contain, some do not,if it exists then only value should be shown.

Can you please help me with this.

<Filter>
<Expression>
<Expression Name="Business">
<Path>
<RolePathItem>
<RoleID>Gcea34afc</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<AttributeID>3d0534a20d19</AttributeID>
</AttributeRef>
</Expression>
<Expression>
<Literal>
<DataType>String</DataType>
<Value>XYZ</Value>
</Literal>
</Expression>
</Expression>




<Expression>
<Function>
<FunctionName>Equals</FunctionName>
<Arguments>
<Expression Name="Product">
<Path>
<RolePathItem>
<RoleID>6a99c8cd92fc</RoleID>
</RolePathItem>
<RolePathItem>
<RoleID>011e01b51ba0</RoleID>
</RolePathItem>
</Path>
</Expression>
<Expression>
<Literal>
<DataType>String</DataType>
<Value>STANDARD</Value>
</Literal>
</Expression>
</Arguments>
</Function>
</Expression>



<Expression>
<Function>
<FunctionName>In</FunctionName>
<Arguments>
<Expression Name="Trend">
<Path>
<RolePathItem>
<RoleID>6a99c8cd92fc</RoleID>
</RolePathItem>
<RolePathItem>
<RoleID>dad362a5a954</RoleID>
</RolePathItem>
</Path>
</Expression>
<Expression>
<Literal>
<DataType>String</DataType>
<Values>
<Value>Active</Value>
<Value>Active New</Value>
<Value>New</Value>
<Value>Sparse</Value>
</Values>
</Literal>
</Expression>
</Arguments>
</Function>
</Expression>
</Filter>

Xml string has errors. For example first <RoleID> not have close tag. Could you correct? — Serkan Arslan
– Serkan Arslan, Commented Sep 4, 2017 at 18:47

Serkan Arslan · Accepted Answer · 2017-09-05 09:19:35Z

Because of dynamic content, I had to use some string functions, but it looks working.

DECLARE @xmlData XML = '
<Filter>
    <Expression>
        <Expression Name="Business">
            <Path>
                <RolePathItem>
                    <RoleID>Gcea34afc</RoleID>
                </RolePathItem>
            </Path>
            <AttributeRef>
                <AttributeID>3d0534a20d19</AttributeID>
                </AttributeRef>
        </Expression>
        <Expression>
            <Literal>
                <DataType>String</DataType>
                <Value>XYZ</Value>
            </Literal>
        </Expression>
    </Expression>
    <Expression>
        <Function>
            <FunctionName>Equals</FunctionName>
            <Arguments>
                <Expression Name="Product">
                    <Path>
                        <RolePathItem>
                            <RoleID>6a99c8cd92fc</RoleID>
                        </RolePathItem>
                        <RolePathItem>
                            <RoleID>011e01b51ba0</RoleID>
                        </RolePathItem>
                    </Path>
                </Expression>
                <Expression>
                    <Literal>
                        <DataType>String</DataType>
                        <Value>STANDARD</Value>
                    </Literal>
                </Expression>
            </Arguments>
        </Function>
    </Expression>
    <Expression>
        <Function>
            <FunctionName>In</FunctionName>
            <Arguments>
                <Expression Name="Trend">
                    <Path>
                        <RolePathItem>
                            <RoleID>6a99c8cd92fc</RoleID>
                        </RolePathItem>
                        <RolePathItem>
                            <RoleID>dad362a5a954</RoleID>
                        </RolePathItem>
                    </Path>
                </Expression>
                <Expression>
                    <Literal>
                        <DataType>String</DataType>
                        <Values>
                            <Value>Active</Value>
                            <Value>Active New</Value>
                            <Value>New</Value>
                            <Value>Sparse</Value>
                        </Values>
                    </Literal>
                </Expression>
            </Arguments>
        </Function>
    </Expression>
</Filter>'

;WITH XmlRows AS (
    SELECT 
        CONVERT(VARCHAR(MAX),ref.query('*')) XMLString
    FROM @xmlData.nodes('/Filter/Expression') x ( ref )
)
,NameAndValueXml AS 
    ( SELECT CONVERT(XML, 
                SUBSTRING(XMLString, 
                    CHARINDEX('<Expression Name=',XMLString),
                    ( CHARINDEX('</Expression>',XMLString) - CHARINDEX('<Expression Name=',XMLString) + LEN('</Expression>') )  ) 
                ) AS NameXml 
            ,CONVERT(XML, 
                SUBSTRING(XMLString, 
                    CHARINDEX('<Literal>',XMLString),
                    ( CHARINDEX('</Literal>',XMLString) - CHARINDEX('<Literal>',XMLString) + LEN('</Literal>') )  ) 
                ) 
                AS ValueXml 
            FROM XmlRows
)
SELECT 
    NameXml.value('(./Expression/@Name)[1]', 'nvarchar(255)') Name, 
    CASE 
        WHEN ValueXml.exist('(/Literal/Values/*)') = 1 THEN 
            REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(max),ValueXml.query('(/Literal/Values/*)')),'</Value><Value>' , ','),'</Value>',''),'<Value>','')
        ELSE 
            ValueXml.value('(./Literal/Value)[1]', 'nvarchar(max)') 
        END AS Value
FROM NameAndValueXml

result:

Name        Value
--------    ----------------------------
Business    XYZ
Product     STANDARD
Trend       Active,Active New,New,Sparse

Collectives™ on Stack Overflow

XML parsing using SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related