CODEWITHSUNDEEP
sql-serverxml
Heiko
Heiko

Reputation: 3

Query XML column - get value based on other value in the same row

in my database I have an XML-column that looks like this:

<Root>
  <Row>
    <Einheit>Stck</Einheit>
    <Faktor>1</Faktor>
    <VkPreisEinheit>1</VkPreisEinheit>
    <VkMengenEinheit>1</VkMengenEinheit>
    <EkMengenEinheit>1</EkMengenEinheit>
    <StcklEinheit>1</StcklEinheit>
    <StcklDefinition>1</StcklDefinition>
    <KonsumentenEinheit>1</KonsumentenEinheit>
  </Row>
  <Row>
    <Einheit>Stück</Einheit>
    <Faktor>100</Faktor>
    <EinheitFaktor>Stck</EinheitFaktor>
    <EkPreisEinheit>1</EkPreisEinheit>
  </Row>
</Root>

What I want to achieve ist that I get the value from 'Faktor' only from the row where 'EkPreisEinheit' is 1

I have tried something with:

CASE WHEN isnull(convert(xml,xmlcolumn).value('(/Root/Row/EkPreisEinheit)[1]','nvarchar(max)'),'') = '1'     
     THEN isnull(convert(xml,xmlcolumn).value('(/Root/Row/Faktor)[1]','nvarchar(max)'),'')
     WHEN isnull(convert(xml,xmlcolumn).value('(/Root/Row/EkPreisEinheit)[2]','nvarchar(max)'),'') = '1'
     THEN isnull(convert(xml,xmlcolumn).value('(/Root/Row/Faktor)[2]','nvarchar(max)'),'')
     ELSE ''  
     END AS Faktor

which would work if EKPreiseinheit would be found in both columns, but it only is in one. Also it could be that it is in the first row, or in the third if there was any. Is there any way to tackle this?

Upvotes: 0

Views: 73

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

Here is a solution for you. Few points to mention. (0) It handles XML directly from the table. (1) It converts XML data into XML data type via TRY_CAST(). So it will emit NULL when XML is not well-formed without generating any error. (2) It checks via XPath predicate for the <Row> element where 'EkPreisEinheit' element value is 1, and filters out anything else.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, xmlcolumn NVARCHAR(MAX));
INSERT INTO @tbl
VALUES (N'<Root>
    <Row>
        <Einheit>Stck</Einheit>
        <Faktor>1</Faktor>
        <VkPreisEinheit>1</VkPreisEinheit>
        <VkMengenEinheit>1</VkMengenEinheit>
        <EkMengenEinheit>1</EkMengenEinheit>
        <StcklEinheit>1</StcklEinheit>
        <StcklDefinition>1</StcklDefinition>
        <KonsumentenEinheit>1</KonsumentenEinheit>
    </Row>
    <Row>
        <Einheit>Stück</Einheit>
        <Faktor>100</Faktor>
        <EinheitFaktor>Stck</EinheitFaktor>
        <EkPreisEinheit>1</EkPreisEinheit>
    </Row>
</Root>');
-- DDL and sample data population, end

;WITH rs AS
(
   SELECT ID, TRY_CAST(xmlcolumn AS XML) AS xml_data
   from @tbl
)
SELECT ID
    , col.value('(Faktor/text())[1]','INT') AS Faktor
    , col.value('(EkPreisEinheit/text())[1]','INT') AS EkPreisEinheit
FROM rs as tbl
    CROSS APPLY tbl.xml_data.nodes('/Root/Row[EkPreisEinheit="1"]') AS tab(col);

Output

+----+--------+----------------+
| ID | Faktor | EkPreisEinheit |
+----+--------+----------------+
|  1 |    100 |              1 |
+----+--------+----------------+

Upvotes: 0

Thom A
Thom A

Reputation: 95554

When dealing with multiple of the same node, you want to use nodes in the FROM to create a row for each one. This means you end up with something like this:

DECLARE @XML xml = '<Root>
  <Row>
    <Einheit>Stck</Einheit>
    <Faktor>1</Faktor>
    <VkPreisEinheit>1</VkPreisEinheit>
    <VkMengenEinheit>1</VkMengenEinheit>
    <EkMengenEinheit>1</EkMengenEinheit>
    <StcklEinheit>1</StcklEinheit>
    <StcklDefinition>1</StcklDefinition>
    <KonsumentenEinheit>1</KonsumentenEinheit>
  </Row>
  <Row>
    <Einheit>Stück</Einheit>
    <Faktor>100</Faktor>
    <EinheitFaktor>Stck</EinheitFaktor>
    <EkPreisEinheit>1</EkPreisEinheit>
  </Row>
</Root>';

WITH YourTable AS(
    SELECT V.YourXML
    FROM (VALUES(@XML)) V(YourXML))
SELECT R.R.value('(EkPreisEinheit/text())[1]','int') AS EkPreisEinheit
FROM YourTable YT
     --Due to the misuse of datatypes you'll need a CONVERT in a VALUES clause here instead
     CROSS APPLY YT.YourXML.nodes('/Root/Row') R(R)
WHERE R.R.value('(EkPreisEinheit/text())[1]','int') IS NOT NULL;

Upvotes: 1

Related Questions