Select values from the XML field in SQL Server 2008

Question

Select values from the XML field in SQL Server 2008

Navigation

#1 by (3 votes)

2

I have a table in SQL that has two fields ( id , xml ), just looking at my XML field, the row is like this:

<Sheet1>
    <Production_x0020_Line>L1B4</Production_x0020_Line>
    <Item_x0020_Number>VPJG7F-10849-BD</Item_x0020_Number>
    <Fecha>02/20 - 02/26</Fecha>
    <cantidad>2</cantidad>
</Sheet1>
<Sheet1>
    <Production_x0020_Line>L1B4</Production_x0020_Line>
    <Item_x0020_Number>VPJG7F-10849-DD</Item_x0020_Number>
    <Fecha>02/20 - 02/26</Fecha>
    <cantidad>1</cantidad>
</Sheet1>

I would like you to see a normal table in SQL like this:

Production line Item Number     Date   Cantidad 
L1B4            VPJG7F10849BD   02/20  2        
L1B4            VPJG7F10849DD   02/20  1

Having the data in a table to be able to make a query and select L1B4 and to add the quantities 2 and 1, or I would like to know if there is any other way to do it.

sql sql-server xml sql-server-2008

asked by abi 06.04.2017 в 16:44

source

1 answer

How to compare elements of two JavaScript arrays? How can you download a file with Python with multiple connections (for example: something like the IDM download manager)?

score 3 · Accepted Answer

The following script can help you:

CREATE TABLE TablaXML (
    Id INT,
    ContenidoXML XML
)

INSERT INTO TablaXML (Id, ContenidoXML) VALUES (1, '<Sheet1><Production_x0020_Line>L1B4</Production_x0020_Line><Item_x0020_Number>VPJG7F-10849-BD</Item_x0020_Number><Fecha>02/20 - 02/26</Fecha><cantidad>2</cantidad></Sheet1><Sheet1><Production_x0020_Line>L1B4</Production_x0020_Line><Item_x0020_Number>VPJG7F-10849-DD</Item_x0020_Number><Fecha>02/20 - 02/26</Fecha><cantidad>1</cantidad></Sheet1>')

SELECT * FROM TablaXML

DECLARE @xml XML

SELECT @xml = ContenidoXML FROM TablaXML

SELECT  
       Tabla.Columna.value('Production_x0020_Line[1]', 'VARCHAR(64)') AS 'Production line',
       Tabla.Columna.value('Item_x0020_Number[1]', 'VARCHAR(64)') AS 'Item Number',
       Tabla.Columna.value('Fecha[1]', 'VARCHAR(64)') AS 'Date',
       Tabla.Columna.value('cantidad[1]', 'smallint') AS 'Quantity'
FROM   @xml.nodes('//Sheet1') Tabla(Columna)

Here you can see the demo and its results.