Striping All values form XML string using TSQL (SQL SERVER 2008)

Question

I'm looking into a problem where i need to be comparing a given XML to what user inputs without the values, so for example if this is the user input:

    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
        <soapenv:Body>
            <genRetrieve xmlns:v1="http://xxxxxxxxxxxxxxxxxxxxx">
                <checkRetrieve>
                    <party>
                        <user>
                            <first>BLA</first>
                            <last>last</last>
                        </user>
                        <media>none</media>
                    </party>
                </checkRetrieve>
            </genRetrieve>
        </soapenv:Body>
    </soapenv:Envelope>

I need to produce the following and insert it into another table:

  <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
        <soapenv:Body>
            <genRetrieve xmlns:v1="http://xxxxxxxxxxxxxxxxxxxxx">
                <checkRetrieve>
                    <party>
                        <user>
                            <first></first>
                            <last></last>
                        </user>
                        <media></media>
                    </party>
                </checkRetrieve>
            </genRetrieve>
        </soapenv:Body>
    </soapenv:Envelope>

without the formatting (spaces, line feeds ...etc).

Can anyone please give an example on how this could be archived? I intially used the blow metoh:

    DECLARE @hdoc     int
    DECLARE @doc      varchar(2000)

    SET @doc = 'my xml'

    EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

    SELECT * FROM OPENXML( @hdoc, '/*',2)

    EXEC sp_xml_removedocument @hdoc
    GO

but it seemes tricky to build the final XML string using the output of the table from above command, if you could show me the standard/most efficient way (given there will xml strings with many elements in them) it would be much appreciated.

Are you sure you've selected the correct tool for the job? I'm sure that there's something sitting above the database code that is sending this data in and is probably a more fully-featured language than T-SQL and has access to more regular XML processing libraries. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 12, 2014 at 7:49

Mikael Eriksson · Accepted Answer · 2014-03-12 07:50:43Z

You can use the XML data type and use delete (XML DML) to remove all text() nodes.

declare @xml xml = 
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  <soapenv:Body>
    <genRetrieve xmlns:v1="http://xxxxxxxxxxxxxxxxxxxxx">
      <checkRetrieve>
        <party>
          <user>
            <first>BLA</first>
            <last>last</last>
          </user>
          <media>none</media>
        </party>
      </checkRetrieve>
    </genRetrieve>
  </soapenv:Body>
</soapenv:Envelope>'

set @xml.modify('delete //*/text()')

select @xml

Result:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  <soapenv:Body>
    <genRetrieve xmlns:v1="http://xxxxxxxxxxxxxxxxxxxxx">
      <checkRetrieve>
        <party>
          <user>
            <first />
            <last />
          </user>
          <media />
        </party>
      </checkRetrieve>
    </genRetrieve>
  </soapenv:Body>
</soapenv:Envelope>

Collectives™ on Stack Overflow

Striping All values form XML string using TSQL (SQL SERVER 2008)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related