Select data from XML with namespace

Question

I am newbie to SQL and XML. My aim is to get the data from all the elements within the Client block but the SELECT not returning any data. Without the namespace the query works.

Can you help?

DECLARE @x XML='<BusinessEvent Name="FO.Client">
            <ClientMessage xmlns="http://www.fwbs.net/Aderant.BO.Integration/FO.MessageTypes.xsd"> 
                <OriginatingProcessIdentity></OriginatingProcessIdentity>
                <Operation></Operation>
                <IsDataIncluded></IsDataIncluded>
                <Clients>
                    <Client>
                        <GlobalId></GlobalId>
                        <ClID>g</ClID>
                        <No></No>
                        <Type></Type>
                        <Name></Name>
                        <BrCode></BrCode>
                        <Created></Created>
                        <FeeUsrID></FeeUsrID>
                        <DefaultAddID></DefaultAddID>
                        <ContID></ContID>
                    </Client>
                </Clients>
            </ClientMessage>
        </BusinessEvent>'


     ;WITH XMLNAMESPACES
         (DEFAULT 'http://www.fwbs.net/Aderant.BO.Integration/FO.MessageTypes.xsd')         
        SELECT TOP 10 cl.c.value('(text())[1]', 'varchar(20)') clid
    FROM (SELECT @x data) data
    CROSS APPLY [data].nodes('/BusinessEvent/ClientMessage/Clients/Client/ClID') AS cl(c)

The answer @marc_s gave you helps you with the namespace (Although your solution with DEFAULT was OK, if you changed nothing but ....nodes('/*:BusinessEvent..., to wildcard the namespace of the outer-most node.) Just one hint: As you want to get the data from all the elements within the Client block, you should stop the XPath in .nodes() after client to read each element with c.value('ClID[1]','nvarchar(max)'). This allows you to address all inner elements with the same CROSS APPLY. — Gottfried Lesigang
– Gottfried Lesigang, Commented Dec 1, 2016 at 16:18

marc_s · Accepted Answer · 2016-12-01 11:49:47Z

3

Since the namespace is not on the root element, and thus does not apply to the whole XML document, you cannot define it as a "default" namespace in your T-SQL code - you need to be specific:

;WITH XMLNAMESPACES ('http://www.fwbs.net/Aderant.BO.Integration/FO.MessageTypes.xsd' AS ns)         
SELECT TOP 10 
    cl.c.value('(text())[1]', 'varchar(20)') clid
FROM 
    @x.nodes('/BusinessEvent/ns:ClientMessage/ns:Clients/ns:Client/ns:ClID') AS cl(c)

The <BusinessEvent> element is not part of that XML namespace - but if you define it as the DEFAULT namespace in your T-SQL statement, that default namespace will be applied to all of your XML elements as defined in your XPath - so the <BusinessEvent> top level node is NOT matched....

answered Dec 1, 2016 at 11:49

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

Good catch with the namespace, +1 from my side. I think, the XPath should not go down to ns:ClID, but stop after ns:Client to get hands on the inner elements in one go (see my comment to OP).

Collectives™ on Stack Overflow

Select data from XML with namespace

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related