Extract data values from XML column in SQL Server database

Question

I have a SQL Server database with a table containing a XML column. I want to extract data from it but we are using prefix on the name.

Here is an example:

<cfdi:comprobante xmlns:cfdi="" total="" tax="">
     <cfdi:sender information="" moreinformation=""/>
     <cfdi:receiver information="" moreinformation=""/>
     <cfdi:addments>
            <another:payment information="" moreinformation="">
                <another:Movements date="" description="hello" amount="100.00" contract="10"/>
                <another:Movements date="" description="hello2" amount="200.00" contract="20"/>
                <another:Movements date="" description="bye" amount="300.00" contract="30"/>
                <another:Movements date="" description="bye2" amount="400.00" contract="40"/>
            </another:payment>
     </cfdi:addments>
</cfdi:comprobante>

What I need is a query that extracts the Movements that looks like these:

ID(other column	Contract	amount	Description
39098	10	100.00	hello
39098	20	200.00	hello2
39098	30	300.00	bye
39098	40	400.00	bye2

I hope I was clear enough. Please really need help on this one.

Thank you in advance.

Those "prefixes" on the XML elements are XML namespaces - but in order to present a query to do what you want, we'll need to see where those XML namespaces are defined (like xmlns:cfdi - the others are probably somewhere earlier in the XML data) — marc_s
– marc_s, Commented Mar 26, 2021 at 19:21
Hi! thank you for the reply, I don't really have permission to check on those I have them like this: xmlns:cfdi="sat.gob.mx/cfd/3" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:another="pegasotecnologia.com/secfd/Schemas" — Alejandro Malagon
– Alejandro Malagon, Commented Mar 26, 2021 at 22:24

marc_s · Accepted Answer · 2021-03-27 06:23:28Z

1

You need something like this - taking into account the XML namespaces defined; I cannot show the exact code, since you haven't shown us the place where your XML namespaces are defined (xmlns:cfdi="sat.gob.mx/cfd/3" and xmlns:another="pegasotecnologia.com/secfd/Schemas"):

WITH XMLNAMESPACES('sat.gob.mx/cfd/3' AS c,
                   'pegasotecnologia.com/secfd/Schemas' AS a)
SELECT 
    ID,
    XC.value('@contract', 'int'),
    XC.value('@amount', 'decimal(16,2)'),
    xc.value('@description', 'varchar(100)')
FROM 
    dbo.YourTable
CROSS APPLY 
    XmlColumn.nodes('/c:comprobante/c:addments/a:payment/a:Movements') AS XT(XC)

If you get the XML namespaces right, you should get an output something like this:

edited Mar 27, 2021 at 6:23

answered Mar 26, 2021 at 19:27

marc_s

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

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

2 Comments

Alejandro Malagon Over a year ago

Hi! thank you for the reply, I don't really have permission to check on those I have them like this: xmlns:cfdi="sat.gob.mx/cfd/3" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:another="pegasotecnologia.com/secfd/Schemas"

Phil Blackburn Over a year ago

To me, this look like the correct answer. Hint: don't forget to include a semi-colon is present before the with statement.

Alan Burstein · Accepted Answer · 2021-03-26 20:50:05Z

First the XML Logic.

DECLARE @XML XML = 
'<cfdi:comprobante xmlns:cfdi="xxx.yyy.com" total="xxx.yyy.com" tax="xxx.yyy.com" xmlns:another="abc.com" amount="fff.net" contract="zzz.zzz" payment="ddd.com">
     <cfdi:sender information="" moreinformation=""/>
     <cfdi:receiver information="" moreinformation=""/>
     <cfdi:addments>
            <another:payment information="" moreinformation="">
                <another:Movements date="" description="hello" amount="100.00" contract="10"/>
                <another:Movements date="" description="hello2" amount="200.00" contract="20"/>
                <another:Movements date="" description="bye" amount="300.00" contract="30"/>
                <another:Movements date="" description="bye2" amount="400.00" contract="40"/>
            </another:payment>
     </cfdi:addments>
</cfdi:comprobante>';

SELECT 
  [Contract]    = f2.Nd.value('(@contract)[1]', 'int'),
  Amount        = f2.Nd.value('(@amount)[1]', 'money'),
  [Description] = f2.Nd.value('(@description)[1]', 'varchar(100)')
FROM (VALUES(@XML)) AS f(X)
CROSS APPLY f.X.nodes('//*:Movements') AS f2(Nd);

Returns:

Contract    Amount     Description
----------- ---------- -----------------
10          100.00     hello
20          200.00     hello2
30          300.00     bye
40          400.00     bye2

Against a table requires a small tweak:

DECLARE @sometable TABLE
(
  SomeId  INT IDENTITY,
  SomeXML XML
);

INSERT @sometable VALUES(
'<cfdi:comprobante xmlns:cfdi="xxx.yyy.com" total="xxx.yyy.com" tax="xxx.yyy.com" xmlns:another="abc.com" amount="fff.net" contract="zzz.zzz" payment="ddd.com">
     <cfdi:sender information="" moreinformation=""/>
     <cfdi:receiver information="" moreinformation=""/>
     <cfdi:addments>
            <another:payment information="" moreinformation="">
                <another:Movements date="" description="hello" amount="100.00" contract="10"/>
                <another:Movements date="" description="hello2" amount="200.00" contract="20"/>
                <another:Movements date="" description="bye" amount="300.00" contract="30"/>
                <another:Movements date="" description="bye2" amount="400.00" contract="40"/>
            </another:payment>
     </cfdi:addments>
</cfdi:comprobante>'),
(
'<cfdi:comprobante xmlns:cfdi="xxx.yyy.com" total="xxx.yyy.com" tax="xxx.yyy.com" xmlns:another="abc.com" amount="fff.net" contract="zzz.zzz" payment="ddd.com">
     <cfdi:sender information="" moreinformation=""/>
     <cfdi:receiver information="" moreinformation=""/>
     <cfdi:addments>
            <another:payment information="" moreinformation="">
                <another:Movements date="" description="Hi!" amount="400.00" contract="99"/>
                <another:Movements date="" description="Sup" amount="1100.00" contract="50"/>
                <another:Movements date="" description="Boooooo" amount="3100.00" contract="30"/>
            </another:payment>
     </cfdi:addments>
</cfdi:comprobante>');

Returns:

SomeId   Contract    Amount       Description
-------- ----------- ------------ --------------------
1        10          100.00       hello
1        20          200.00       hello2
1        30          300.00       bye
1        40          400.00       bye2
2        99          400.00       Hi!
2        50          1100.00      Sup
2        30          3100.00      Boooooo

Hi! This has actually work, because I have to do this with severals values on the column, do you know a way of declaring the xml value as the field on the table, like putting this: DECLARE @XML XML = (select g_xml_comprobante from table.name where id=1;

Collectives™ on Stack Overflow

Extract data values from XML column in SQL Server database

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related