-1

I have xml data stored in one column of my table. Column datatype is nvarchar(max).

I need to query data from the column to extract some nodes information. Below is the example xml data stored

<Response>
        <Quote>
            <ID>1</ID>
                <Item>
                    <ItemNumber>123456</ItemNumber>
                </Item>
                <Price>
                    <LPrice>12</LPrice>
                </Price>
                <Stocks>
                    <Stock>
                        <Quantity>2</Quantity>
                    </Stock>
                    <Stock>
                        <Quantity>1</Quantity>
                    </Stock>
                </Stocks>
        </Quote>
        <Quote>
            <ID>2</ID>
                <Item>
                    <ItemNumber>234121</ItemNumber>
                </Item>
                <Price>
                    <ListPrice>34</ListPrice>
                </Price>
                <Stocks>
                    <Stock>
                        <Quantity>4</Quantity>                      
                    </Stock>
                    <Stock>
                        <Quantity>2</Quantity>
                    </Stock>                    
                </Stocks>
        </Quote>
</Response>

I want to get the information in the below format:

Item Number LPrice Quantity
123456 12 3
234121 34 6

Please help.

CellX
  • 11
  • 3
  • 2
    Any reason you can't change the column type to XML? – Tomalak Jul 09 '21 at 06:45
  • 1
    Also, what have you tried? Link to questions/posts you have read, show code you have written, cite any errors, etc. – Tomalak Jul 09 '21 at 06:46
  • @Tomalak I Cannot change the column type to xml as it has special characters that xml does not support and it will require a lot of changes. – CellX Jul 09 '21 at 07:19
  • @Tomalak I tried converting the column to XML and then use column.query('data(path)') but the problem is that it return the value in row with a space in between for both LPrice. I want them to be returned in different rows. Also, I tried column.query(sum(path)), it return the sum for all and not differentiate for each ID – CellX Jul 09 '21 at 07:23
  • *"as it has special characters that xml does not support"* - No, it doesn't. A) How did the XML documents get in there if there were any special characters that XML does not support? B) There are no characters that XML documents support and the XML data type in SQL server does not. 3) No, it literally only takes a single"ALTER TABLE" statement to change the column and make everyone's life easier. – Tomalak Jul 09 '21 at 07:25
  • I don't see any unsupported characters in your example XML? – Stu Jul 09 '21 at 07:33
  • @Stu If it's XML, there are no unsupported characters. If there are unsupported characters, it's not XML. – Tomalak Jul 09 '21 at 07:43
  • @Stu, I tried to query and it returned with error Illegal characters. We cannot change the column type as of now. – CellX Jul 09 '21 at 07:43
  • 1
    Presumably though, if you `cast` your nvarchar data as xml in order to query it, you'll still get the same error. Create a [DBFiddle](https://dbfiddle.uk/) to demonstrate the error. – Stu Jul 09 '21 at 07:57

1 Answers1

0

It is perfectly fine to store xml data in a nvarchar()/varbinary() column as it is the only way to maintain the fidelity of the source information.

declare @t table(x nvarchar(max))

insert into @t(x)
values(N'<Response>
    <Quote>
        <ID>1</ID>
            <Item>
                <ItemNumber>123456</ItemNumber>
            </Item>
            <Price>
                <ListPrice>12</ListPrice>
            </Price>
            <Stocks>
                <Stock>
                    <Quantity>2</Quantity>
                </Stock>
                <Stock>
                    <Quantity>1</Quantity>
                </Stock>
            </Stocks>
    </Quote>
    <Quote>
        <ID>2</ID>
            <Item>
                <ItemNumber>234121</ItemNumber>
            </Item>
            <Price>
                <ListPrice>34</ListPrice>
            </Price>
            <Stocks>
                <Stock>
                    <Quantity>4</Quantity>                      
                </Stock>
                <Stock>
                    <Quantity>2</Quantity>
                </Stock>                    
            </Stocks>
    </Quote>
</Response>');


select 
    n.q.value('(Item/ItemNumber/text())[1]', 'nvarchar(50)'),
    n.q.value('(Price/ListPrice/text())[1]', 'nvarchar(50)'),
    n.q.query('sum(Stocks/Stock/Quantity)').value('.', 'nvarchar(50)')
from @t as t
cross apply(select try_cast(t.x as xml) as xx) as c
cross apply c.xx.nodes(N'Response/Quote') as n(q);
lptr
  • 1
  • 2
  • 6
  • 16
  • Thanks but I am confused by this actually. I am still learning this, you posted the query by inserting the xml into table and since the data is already in my table, how do I do that for all rows ? I tried to use cross apply when I do not use declare and insert statement but it does not work since my column is not of xml type. – CellX Jul 09 '21 at 07:55
  • ..@CellX..replace @t with your [table name] in the select statement..and you might want to add a where clause.. – lptr Jul 09 '21 at 07:59
  • I executed the following and it is giving 0 results. Can you please suggest ? – CellX Jul 12 '21 at 10:23